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