SQL Server 2005 is used to manipulate XML files and their data (very comprehensive) _mssql2005

--Multiple data can be composed of more than one XML tree l insert
--Incoming XML tree as varchar parameter
Syntax for--insert xx select xxx from OPENXML () Insert data
-----------------------------------import, export XML--------------------------

--1 Import Instance
--Single table
CREATE table Xmltable (Name nvarchar, 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
-----------------------Read the second table data--------------------
CREATE table Xmlta (Name nvarchar, nowtime nvarchar (20))
Declare @s as nvarchar (4000);
Set @s =n '
<Xmltables>
&LT;XMLTB name= "6" nowtime= "1900-2-1" >0</Xmltable>
<xmlta name= "One" 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
-----------------------read multiple table data at the same time----------------
CREATE table Xmlta (Name nvarchar, nowtime datetime)
CREATE table XMLTB (Name nvarchar, nowtime datetime)
Declare @s as nvarchar (4000);
Set @s =n '
<Xmltables>
<xmlta name= "1" nowtime= "1900-2-1" >0</Xmlta>
&LT;XMLTB name= "2" nowtime= "1900-2-1" >0</Xmltb>
</Xmltables>
'';
--<xmlta ></Xmlta> the inserted data is null
declare @idHandle as int;
EXEC sp_xml_preparedocument @idHandle OUTPUT, @s
--Table A
Insert into Xmlta (name,nowtime)
SELECT * FROM OpenXML (@idHandle, N '/xmltables/xmlta ')
With dbo. Xmlta
--Table 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
--Generate an XML file sheet
DECLARE @xVar XML
SET @xVar = (SELECT * from Xmltable for XML Auto,type)
Select @xVar


--1 read XML file Insert Table
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, Intro nvarchar (20))
EXEC sp_xml_removedocument @hdoc
--2 read XML file Insert Table
SELECT * into #temp the 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, Intro nvarchar (20))
EXEC sp_xml_removedocument @hdoc
/*
Processing of---Empty
<dbo.xmltable name= "1" intro= ""/>
<dbo.xmltable name= "2"/>
<dbo.xmltable name= "3" intro= "C"/>
1
2 NULL
3 C
*/
drop table XMLT
------------------------------------XML Data Manipulation------------------
--Typed XML
CREATE TABLE xmlt (ID INT PRIMARY KEY, xcol XML not NULL)
--t-sql Generate Data
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 Generate Data
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> ')
--Read the name=1 node, please use
SELECT xcol.query ('/xmltables/xmltable[@Name = ' 1] ') from XMLT where ID =1
--Read Name=1 's node values, use
SELECT xcol.query ('/xmltables/xmltable[@Name = ' 1 "]/text () ') from XMLT where ID =1
--Read the name of the name=5 value, please use
SELECT xcol.query (' Data (/xmltables/xmltable[@Name]) [5] ') from XMLT where ID =1
--Read all node name
SELECT nref.value (' @Name ', ' varchar (max) ') LastName
From XMLT CROSS APPLY xcol.nodes ('/xmltables/xmltable ') as R (nref) where id=1
--Read all nodes 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
--Read the name of the name=1 value
SELECT xcol.value (' Data (/xmltables/xmltable//name) [1] ', ' nvarchar (max) ') from XMLT where id=2
--Read the Nowtime value of nowtime=1
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

------------------------------------------function uses----------------
--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" >
</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 () and nodes (), modify ()
SELECT CAST (t.c as XML). Query ('/root/dbo.xmltable/name ')
From OPENROWSET (BULK ' E:\xml.xml ', Single_blob) T (c)