Xml Element and data insert ,deletion ,updation


/ Published in: SQL
Save to your folder(s)



Copy this code and paste it in your HTML
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.