Posted By

kashif21 on 04/14/11


Tagged

insert data element xml and deletion updation


Versions (?)

Xml Element and data insert ,deletion ,updation


 / Published in: SQL
 

  1. Assume this IS your XML snippet
  2. ?
  3. 1
  4. 2
  5. 3
  6. 4
  7. 5
  8. 6
  9. 7
  10. 8
  11. 9
  12. 10
  13. 11
  14. 12
  15. 13
  16. 14 DECLARE @authorsXML XML
  17.  
  18. SET @authorsXML = '
  19. <Author>
  20. <ID>172-32-1176</ID>
  21. <LastName>White</LastName>
  22. <FirstName>Johnson</FirstName>
  23. <Address>
  24. <Street>10932 Bigge Rd.</Street>
  25. <City>Menlo Park</City>
  26. <State>CA</State>
  27. </Address>
  28. </Author>
  29. '
  30. Note that the examples below SHOW how you can manipulate XML nodes – but most operations require singleton VALUES. Ie, the changes must affect one AND only one node. Thus IN most the examples we specify the INDEX of the node we want TO target.
  31. FOR example:
  32. (/Author/LastName)[1]
  33. which means we are only targetting the first instance of LastName under the Author node. IF you need TO do a mass UPDATE, you may need TO USE a cursor.
  34. TO ADD an element AS the last node
  35. ?
  36. 1
  37. 2
  38. 3
  39. 4
  40. 5
  41. 6
  42. 7
  43. 8
  44. 9
  45. 10
  46. 11
  47. 12
  48. 13
  49. 14
  50. 15
  51. 16
  52. 17
  53. 18
  54. 19 SET @authorsXML.MODIFY('
  55. insert element Country {"Canada"} as last into
  56. (/Author/Address)[1]
  57. ')
  58. /*
  59. result:
  60.  
  61. <Author>
  62.   <ID>172-32-1176</ID>
  63.   <LastName>White</LastName>
  64.   <FirstName>Johnson</FirstName>
  65.   <Address>
  66.   <Street>10932 Bigge Rd.</Street>
  67.   <City>Menlo Park</City>
  68.   <State>CA</State>
  69.   <Country>Canada</Country>
  70.   </Address>
  71. </Author>
  72. */
  73. TO ADD an element IN a specific position
  74. ?
  75. 1
  76. 2
  77. 3
  78. 4
  79. 5
  80. 6
  81. 7
  82. 8
  83. 9
  84. 10
  85. 11
  86. 12
  87. 13
  88. 14
  89. 15
  90. 16
  91. 17
  92. 18
  93. 19 SET @authorsXML.MODIFY('
  94. insert element MiddleInitial {"A"} after
  95. (/Author/LastName)[1]
  96. ')
  97.  
  98. /*
  99. result:
  100.  
  101. <Author>
  102.   <ID>172-32-1176</ID>
  103.   <LastName>White</LastName>
  104.   <FirstName>Johnson</FirstName>
  105.   <Address>
  106.   <Street>10932 Bigge Rd.</Street>
  107.   <City>Menlo Park</City>
  108.   <State>CA</State>
  109.   </Address>
  110. </Author>
  111. */
  112. TO UPDATE an element’s value based ON a variable value
  113. ?
  114. 1
  115. 2
  116. 3
  117. 4
  118. 5
  119. 6
  120. 7
  121. 8
  122. 9
  123. 10
  124. 11
  125. 12
  126. 13
  127. 14
  128. 15
  129. 16
  130. 17
  131. 18
  132. 19
  133. 20
  134. 21
  135. 22
  136. 23 DECLARE @NewFirstName VARCHAR(20)
  137. SET @NewFirstName = 'Johnny'
  138. SET @authorsXML.MODIFY(
  139. '
  140. replace value of (/Author/FirstName/text())[1]
  141. with sql:variable("@NewFirstName")
  142. ')
  143.  
  144. /*
  145. result:
  146. <Author>
  147.   <ID>172-32-1176</ID>
  148.   <LastName>White</LastName>
  149.   <MiddleInitial>A</MiddleInitial>
  150.   <FirstName>Johnny</FirstName>
  151.   <Address>
  152.   <Street>10932 Bigge Rd.</Street>
  153.   <City>Menlo Park</City>
  154.   <State>CA</State>
  155.   <Country>Canada</Country>
  156.   </Address>
  157. </Author>
  158. */
  159. TO DELETE an element
  160. ?
  161. 1
  162. 2
  163. 3
  164. 4
  165. 5
  166. 6
  167. 7
  168. 8
  169. 9
  170. 10
  171. 11
  172. 12
  173. 13
  174. 14
  175. 15
  176. 16
  177. 17
  178. 18
  179. 19 SET @authorsXML.MODIFY(
  180. '
  181. delete (/Author/MiddleInitial)
  182. ')
  183.  
  184. /*
  185. result:
  186. <Author>
  187.   <ID>172-32-1176</ID>
  188.   <LastName>White</LastName>
  189.   <FirstName>Johnny</FirstName>
  190.   <Address>
  191.   <Street>10932 Bigge Rd.</Street>
  192.   <City>Menlo Park</City>
  193.   <State>CA</State>
  194.   <Country>Canada</Country>
  195.   </Address>
  196. </Author>
  197. */
  198. TO DELETE an element based ON the element value
  199. ?
  200. 1
  201. 2
  202. 3
  203. 4
  204. 5
  205. 6
  206. 7
  207. 8
  208. 9
  209. 10
  210. 11
  211. 12
  212. 13
  213. 14
  214. 15
  215. 16
  216. 17
  217. 18 SET @authorsXML.MODIFY(
  218. '
  219. delete (//*[text()="Canada"])
  220. ')
  221.  
  222. /*
  223. result:
  224. <Author>
  225.   <ID>172-32-1176</ID>
  226.   <LastName>White</LastName>
  227.   <FirstName>Johnny</FirstName>
  228.   <Address>
  229.   <Street>10932 Bigge Rd.</Street>
  230.   <City>Menlo Park</City>
  231.   <State>CA</State>
  232.   </Address>
  233. </Author>
  234. */
  235. TO DELETE an element based ON the element name
  236. ?
  237. 1
  238. 2
  239. 3
  240. 4
  241. 5
  242. 6
  243. 7
  244. 8
  245. 9
  246. 10
  247. 11
  248. 12
  249. 13
  250. 14
  251. 15
  252. 16
  253. 17 SET @authorsXML.MODIFY(
  254. '
  255. delete (//*[local-name()="State"])
  256. ')
  257.  
  258. /*
  259. result:
  260. <Author>
  261.   <ID>172-32-1176</ID>
  262.   <LastName>White</LastName>
  263.   <FirstName>Johnny</FirstName>
  264.   <Address>
  265.   <Street>10932 Bigge Rd.</Street>
  266.   <City>Menlo Park</City>
  267.   </Address>
  268. </Author>
  269. */

Report this snippet  

You need to login to post a comment.