SQL SERVER 2005中使用sql语句对xml 文件和其数据的进行操作(很全面)

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

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

--1导入实例
--单个表
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 FOR XML AUTO,TYPE)

select @xVar

 

 

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

DECLARE @hdoc int

DECLARE @doc xml

select @doc=BulkColumn  from  (SELECT *

 FROM OPENROWSET(BULK ''E:\xml.xml'',SINGLE_BLOB) a)b

EXEC sp_xml_preparedocument @hdoc OUTPUT,@doc

SELECT * into #temp

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

with  (name nvarchar(20),Intro nvarchar(20))

exec sp_xml_removedocument @hdoc

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

SELECT * into #temp FROM OPENROWSET(

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

DECLARE @hdoc int

DECLARE @doc xml

select @doc=BulkColumn  from #temp

EXEC sp_xml_preparedocument @hdoc OUTPUT,@doc

SELECT * into #temp2

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

with  (name nvarchar(20),Intro nvarchar(20))

exec sp_xml_removedocument @hdoc

/*

---空的处理

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

<dbo.xmltable name="2" />

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

1  

2   NULL

3   c

*/

drop table xmlt

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

--类型化的XML

CREATE TABLE xmlt(ID INT PRIMARY KEY, xCol XML not null)

--T-sql生成数据

insert into 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生成数据

insert into 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 CROSS APPLY xCol.nodes(''/Xmltables/Xmltable'') AS R(nref) where ID=1

--讀取所有节点NowTime

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

FROM xmlt CROSS APPLY 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

 

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

--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 CROSS APPLY xCol.nodes(''/Xmltables/Xmltable'') AS R(nref)

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

 SELECT CAST(T.c as xml).query(''/root/dbo.xmltable/name'')

 FROM OPENROWSET(BULK ''E:\xml.xml'',SINGLE_BLOB) T(c)