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

时间：2021-08-14 03:01:15
  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) 
标签：

相关文章