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

1. -用SQL多条可以将多条数据组成一棵XML树L一次插入  
2. --将XML树作为varchar参数传入用  
3. --insert xx select xxx from openxml() 的语法插入数据  
4. -----------------------------------导入，导出xml--------------------------  
5.  
6. --1导入实例  
7. --单个表  
8. create table Xmltable(Name nvarchar(20),Nowtime nvarchar(20))  
9. declare @s as nvarchar(2000);  
10. set @s = N''  
11. <Xmltables>  
12. <Xmltable Name="1" Nowtime="1900-1-1">0</Xmltable>  
13. <Xmltable Name="2" Nowtime="1900-1-1">0</Xmltable>  
14. <Xmltable Name="3" Nowtime="1900-1-1">0</Xmltable>  
15. <Xmltable Name="4" Nowtime="1900-1-1">0</Xmltable>  
16. <Xmltable Name="5" Nowtime="1900-1-1">0</Xmltable>  
17. </Xmltables>'';  
18. declare @idHandle as int ;  
19. EXEC sp_xml_preparedocument @idHandle OUTPUT, @s  
20. insert into Xmltable(Name,Nowtime)  
21. select * from openxml(@idHandle,N''/Xmltables/Xmltable'')  
22. with dbo.xmltable  
23. EXEC sp_xml_removedocument @idHandle  
24. select * from Xmltable  
25. -----------------------读入第二个表数据--------------------  
26. create table Xmlta(Name nvarchar(20),Nowtime nvarchar(20))  
27. declare @s as nvarchar(4000);  
28. set @s =N''  
29. <Xmltables>  
30. <Xmltb Name="6" Nowtime="1900-2-1">0</Xmltable>  
31. <Xmlta Name="11" Nowtime="1900-2-1">0</Xmlta>  
32. </Xmltables>  
33. '';  
34. declare @idHandle as int ;  
35. EXEC sp_xml_preparedocument @idHandle OUTPUT, @s  
36. insert into Xmlta(Name,Nowtime)  
37. select * from openxml(@idHandle,N''/Xmltables/Xmlta'')  
38. with dbo.xmlta  
39. EXEC sp_xml_removedocument @idHandle  
40. select * from Xmlta  
41. drop table Xmlta  
42. -----------------------同时读入多表数据----------------  
43. create table Xmlta(Name nvarchar(20),Nowtime datetime)  
44. create table Xmltb(Name nvarchar(20),Nowtime datetime)  
45. declare @s as nvarchar(4000);  
46. set @s =N''  
47. <Xmltables>  
48. <Xmlta Name="1" Nowtime="1900-2-1">0</Xmlta>  
49. <Xmltb Name="2" Nowtime="1900-2-1">0</Xmltb>  
50. </Xmltables>  
51. '';  
52. --<Xmlta ></Xmlta> 则插入的数据为null  
53. declare @idHandle as int ;  
54. EXEC sp_xml_preparedocument @idHandle OUTPUT, @s  
55. --表a  
56. insert into Xmlta(Name,Nowtime)  
57. select * from openxml(@idHandle,N''/Xmltables/Xmlta'')  
58. with dbo.Xmlta  
59. --表b  
60. insert into Xmltb(Name,Nowtime)  
61. select * from openxml(@idHandle,N''/Xmltables/Xmltb'')  
62. with dbo.Xmltb  
63. EXEC sp_xml_removedocument @idHandle  
64. select * from Xmlta  
65. select * from Xmltb  
66. drop table Xmlta,Xmltb  
67. --生成xml文件单表  
68. DECLARE @xVar XML  
69. SET @xVar = (SELECT * FROM Xmltable FOR XML AUTO,TYPE)  
70. select @xVar  
71.  
72.  
73. --1读取xml文件插入表中  
74. DECLARE @hdoc int  
75. DECLARE @doc xml  
76. select @doc=BulkColumn from (SELECT *  
77. FROM OPENROWSET(BULK ''E:\xml.xml'',SINGLE_BLOB) a)b  
78. EXEC sp_xml_preparedocument @hdoc OUTPUT,@doc  
79. SELECT * into #temp  
80. FROM OPENXML (@hdoc,N''/root/dbo.xmltable'')  
81. with (name nvarchar(20),Intro nvarchar(20))  
82. exec sp_xml_removedocument @hdoc  
83. --2读取xml文件插入表中  
84. SELECT * into #temp FROM OPENROWSET(  
85. BULK ''E:\xml.xml'',SINGLE_BLOB) AS x  
86. DECLARE @hdoc int  
87. DECLARE @doc xml  
88. select @doc=BulkColumn from #temp  
89. EXEC sp_xml_preparedocument @hdoc OUTPUT,@doc  
90. SELECT * into #temp2  
91. FROM OPENXML (@hdoc,N''/root/dbo.xmltable'')  
92. with (name nvarchar(20),Intro nvarchar(20))  
93. exec sp_xml_removedocument @hdoc  
94. /*  
95. ---空的处理  
96. <dbo.xmltable name="1" Intro="" />  
97. <dbo.xmltable name="2" />  
98. <dbo.xmltable name="3" Intro="c" />  
99. 1  
100. 2 NULL  
101. 3 c  
102. */  
103. drop table xmlt  
104. ------------------------------------xml数据操作------------------  
105. --类型化的XML  
106. CREATE TABLE xmlt(ID INT PRIMARY KEY, xCol XML not null)  
107. --T-sql生成数据  
108. insert into xmlt values(1,  
109. ''<Xmltables>  
110. <Xmltable Name="1" NowTime="1900-1-1">1</Xmltable>  
111. <Xmltable Name="2" NowTime="1900-1-2">2</Xmltable>  
112. <Xmltable Name="3" NowTime="1900-1-3">3</Xmltable>  
113. <Xmltable Name="4" NowTime="1900-1-4">4</Xmltable>  
114. <Xmltable Name="5" NowTime="1900-1-5">5</Xmltable>  
115. </Xmltables>'')  
116. --dataset生成数据  
117. insert into xmlt values(2,  
118. ''<?xml version="1.0" encoding="gb2312" ?>  
119. <Xmltables>  
120. <Xmltable><Name>1</Name><NowTime>1900-1-1</NowTime>1</Xmltable>  
121. <Xmltable><Name>2</Name><NowTime>1900-1-2</NowTime>2</Xmltable>  
122. <Xmltable><Name>3</Name><NowTime>1900-1-3</NowTime>3</Xmltable>  
123. </Xmltables>'')  
124. --读取Name=1 的節點，請使用  
125. SELECT xCol.query(''/Xmltables/Xmltable[@Name="1"]'') from xmlt where ID =1  
126. --读取Name=1 的節點值，請使用  
127. SELECT xCol.query(''/Xmltables/Xmltable[@Name="1"]/text()'') from xmlt where ID =1  
128. --读取Name=5 的Name 屬性值，請使用  
129. SELECT xCol.query(''data(/Xmltables/Xmltable[@Name])[5]'') from xmlt where ID =1  
130. --读取所有节点Name  
131. SELECT nref.value(''@Name'', ''varchar(max)'') LastName  
132. FROM xmlt CROSS APPLY xCol.nodes(''/Xmltables/Xmltable'') AS R(nref) where ID=1  
133. --读取所有节点NowTime  
134. SELECT nref.value(''@NowTime'', ''varchar(max)'') LastName  
135. FROM xmlt CROSS APPLY xCol.nodes(''/Xmltables/Xmltable'') AS R(nref) where ID=1  
136. SELECT xCol.query(''data(/Xmltables/Xmltable[@Name=5]/@NowTime)[1]'') from xmlt where ID =1  
137. --读取Name=1 的Name 屬性值  
138. SELECT xCol.value(''data(/Xmltables/Xmltable//Name)[1]'',''nvarchar(max)'') FROM xmlt where ID=2  
139. --读取NowTime=1 的NowTime 屬性值  
140. SELECT xCol.value(''data(/Xmltables/Xmltable/NowTime)[1]'',''nvarchar(max)'') FROM xmlt where ID=2  
141. --SELECT xCol.value(''data(/Xmltables/Xmltable[@Name])[1]'',''nvarchar(max)'') FROM xmlt where ID=2  
142.  
143. ------------------------------------------函数使用----------------  
144. --query()、exist()  
145. SELECT pk, xCol.query(''/root/dbo.xmltable/name'') FROM docs  
146. SELECT xCol.query(''/root/dbo.xmltable/name'') FROM docs  
147. WHERE xCol.exist (''/root/dbo.xmltable'') = 1  
148. --modify()  
149. UPDATE docs SET xCol.modify(''  
150. insert  
151. <section num="2">  
152. <heading>Background</heading>  
153. </section>  
154. after (/doc/section[@num=1])[1]'')  
155. --value()  
156. SELECT xCol.value(''data((/root/dbo.xmltable//name))[2]'',''nvarchar(max)'') FROM docs  
157. where pk=3  
158. --nodes()  
159. SELECT nref.value(''@Name'', ''varchar(max)'') LastName  
160. FROM xmlt CROSS APPLY xCol.nodes(''/Xmltables/Xmltable'') AS R(nref)  
161. --query()、value()、exist() 和nodes()，modify()  
162. SELECT CAST(T.c as xml).query(''/root/dbo.xmltable/name'')  
163. FROM OPENROWSET(BULK ''E:\xml.xml'',SINGLE_BLOB) T(c)