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