SQL 2005 对xml 文件与 xml数据的操作

 网上转载 用来学习之用

由于数据库对xml数据直接处理有很多优势，05也对这方面加强了功能。

        但这方面资料少，所以自己做了一些总结，希望会给大家带来帮助

       --用SQL多条可以将多条数据组成一棵XML树L一次插入
--将XML树作为varchar参数传入用
--insert  xx  select xxx from openxml() 的语法插入数据
-----------------------------------导入，导出xml--------------------------
 

--1导入实例

--单个表

--charry0110(晓风残月)

create table Xmltable(Name nvarchar(20),Nowtime nvarchar(20))

declare @s as nvarchar(2000);

set @s = N'

<Xmltables>

           <Xmltable Name="1" Nowtime="1900-1-1">0</Xmltable>

           <Xmltable Name="2" Nowtime="1900-1-1">0</Xmltable>

           <Xmltable Name="3" Nowtime="1900-1-1">0</Xmltable>

           <Xmltable Name="4" Nowtime="1900-1-1">0</Xmltable>

           <Xmltable Name="5" Nowtime="1900-1-1">0</Xmltable>

</Xmltables>';

declare @idHandle as int ;

EXEC sp_xml_preparedocument @idHandle OUTPUT, @s

insert into Xmltable(Name,Nowtime)

select * from openxml(@idHandle,N'/Xmltables/Xmltable')

with dbo.xmltable

EXEC sp_xml_removedocument @idHandle

select * from Xmltable

 

-----------------------读入第二个表数据--------------------

create table Xmlta(Name nvarchar(20),Nowtime nvarchar(20))

declare @s as nvarchar(4000);

set @s =N'

<Xmltables>

       <Xmltb Name="6" Nowtime="1900-2-1">0</Xmltable>

       <Xmlta Name="11" Nowtime="1900-2-1">0</Xmlta>

</Xmltables>

';

declare @idHandle as int ;

EXEC sp_xml_preparedocument @idHandle OUTPUT, @s

insert into Xmlta(Name,Nowtime)

select * from openxml(@idHandle,N'/Xmltables/Xmlta')

with dbo.xmlta

EXEC sp_xml_removedocument @idHandle

select * from Xmlta

drop table Xmlta

 

-----------------------同时读入多表数据----------------

create table Xmlta(Name nvarchar(20),Nowtime datetime)

create table Xmltb(Name nvarchar(20),Nowtime datetime)

declare @s as nvarchar(4000);

set @s =N'

<Xmltables>

       <Xmlta Name="1" Nowtime="1900-2-1">0</Xmlta>

       <Xmltb Name="2" Nowtime="1900-2-1">0</Xmltb>

</Xmltables>

';

--<Xmlta ></Xmlta> 则插入的数据为null

declare @idHandle as int ;

EXEC sp_xml_preparedocument @idHandle OUTPUT, @s

--表a

insert into Xmlta(Name,Nowtime)

select * from openxml(@idHandle,N'/Xmltables/Xmlta')

with dbo.Xmlta

--表b

insert into Xmltb(Name,Nowtime)

select * from openxml(@idHandle,N'/Xmltables/Xmltb')

with dbo.Xmltb

EXEC sp_xml_removedocument @idHandle

select * from Xmlta

select * from Xmltb

drop table Xmlta,Xmltb

--生成xml文件单表

DECLARE @xVar XML

SET     @xVar =(SELECT*FROM Xmltable FORXMLAUTO,TYPE)

select @xVar

 

 

--1读取xml文件插入表中

DECLARE @hdoc int

DECLARE @doc xml

select @doc=BulkColumn from  (SELECT*

 FROMOPENROWSET(BULK'E:/xml.xml',SINGLE_BLOB) a)b

EXECsp_xml_preparedocument @hdoc OUTPUT,@doc

SELECT*into #temp

FROMOPENXML(@hdoc,N'/root/dbo.xmltable')

with  (namenvarchar(20),Intro nvarchar(20))

execsp_xml_removedocument @hdoc

--2读取xml文件插入表中

SELECT*into #temp FROMOPENROWSET(

BULK'E:/xml.xml',SINGLE_BLOB)AS x

DECLARE @hdoc int

DECLARE @doc xml

select @doc=BulkColumn from #temp

EXECsp_xml_preparedocument @hdoc OUTPUT,@doc

SELECT*into #temp2

FROMOPENXML(@hdoc,N'/root/dbo.xmltable')

with  (namenvarchar(20),Intro nvarchar(20))

execsp_xml_removedocument @hdoc

/*

---空的处理

<dbo.xmltable name="1" Intro="" />

<dbo.xmltable name="2" />

<dbo.xmltable name="3" Intro="c" />

1  

2   NULL

3   c

*/

droptable xmlt

------------------------------------xml数据操作------------------

--类型化的XML

CREATETABLE xmlt(ID INTPRIMARYKEY, xCol XMLnotnull)

--T-sql生成数据

insertinto xmlt values(1,

'<Xmltables>

               <Xmltable Name="1" NowTime="1900-1-1">1</Xmltable>

               <Xmltable Name="2" NowTime="1900-1-2">2</Xmltable>

               <Xmltable Name="3" NowTime="1900-1-3">3</Xmltable>

               <Xmltable Name="4" NowTime="1900-1-4">4</Xmltable>

               <Xmltable Name="5" NowTime="1900-1-5">5</Xmltable>

</Xmltables>')

--dataset生成数据

insertinto xmlt values(2,

'<?xml version="1.0" encoding="gb2312" ?>

<Xmltables>

<Xmltable><Name>1</Name><NowTime>1900-1-1</NowTime>1</Xmltable>

<Xmltable><Name>2</Name><NowTime>1900-1-2</NowTime>2</Xmltable>

<Xmltable><Name>3</Name><NowTime>1900-1-3</NowTime>3</Xmltable>

</Xmltables>')

--讀取Name=1 的節點，請使用

SELECT xCol.query('/Xmltables/Xmltable[@Name="1"]')from xmlt where ID =1

--讀取Name=1 的節點值，請使用

SELECT xCol.query('/Xmltables/Xmltable[@Name="1"]/text()')from xmlt where ID =1

--讀取Name=5 的Name 屬性值，請使用

SELECT xCol.query('data(/Xmltables/Xmltable[@Name])[5]')from xmlt where ID =1

--讀取所有节点Name

SELECT nref.value('@Name','varchar(max)') LastName

FROM xmlt CROSSAPPLY xCol.nodes('/Xmltables/Xmltable')AS R(nref)where ID=1

--讀取所有节点NowTime

SELECT nref.value('@NowTime','varchar(max)') LastName

FROM xmlt CROSSAPPLY xCol.nodes('/Xmltables/Xmltable')AS R(nref)where ID=1

SELECT xCol.query('data(/Xmltables/Xmltable[@Name=5]/@NowTime)[1]')from xmlt where ID =1

--讀取Name=1 的Name 屬性值

SELECT xCol.value('data(/Xmltables/Xmltable//Name)[1]','nvarchar(max)')FROM xmlt where ID=2

--讀取NowTime=1 的NowTime 屬性值

SELECT xCol.value('data(/Xmltables/Xmltable/NowTime)[1]','nvarchar(max)')FROM xmlt where ID=2

--SELECT xCol.value('data(/Xmltables/Xmltable[@Name])[1]','nvarchar(max)') FROM xmlt where ID=2

--讀取Name 屬性值 为一行

SELECT xCol.query('for $Name in //Xmltable/@Name return string($Name)') from xmlt

--1 2 3 4 5

------------------------------------------函数使用----------------

--query()、exist()

SELECT pk, xCol.query('/root/dbo.xmltable/name')FROM docs

SELECT xCol.query('/root/dbo.xmltable/name')FROM docs

WHERE xCol.exist ('/root/dbo.xmltable')= 1

--modify()

UPDATE docs SET xCol.modify('

insert

<section num="2">

<heading>Background</heading>

</section>               

after (/doc/section[@num=1])[1]')

--value()

SELECT xCol.value('data((/root/dbo.xmltable//name))[2]','nvarchar(max)')FROM docs

where pk=3

--nodes()

SELECT nref.value('@Name','varchar(max)') LastName

FROM xmlt CROSSAPPLY xCol.nodes('/Xmltables/Xmltable')AS R(nref)

--query()、value()、exist() 和nodes()，modify()

 SELECTCAST(T.c asxml).query('/root/dbo.xmltable/name')

 FROMOPENROWSET(BULK'E:/xml.xml',SINGLE_BLOB) T(c)