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

–用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)