Posted By

rralhan on 05/23/14


Tagged

sql xml


Versions (?)

Parse xml in SQL


 / Published in: SQL
 

Parse xml (passed as a parameter) in SQL

  1. -- FIRST WAY
  2.  
  3. Declare @xml AS xml;
  4.  
  5. SET @xml =
  6. '<LandLord xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  7. <Address1>1092 Bel Lido Dr</Address1>
  8. <City>Highland Beach</City>
  9. <County>Palm Beach County</County>
  10. <State>FL</State>
  11. <Zip>33487</Zip>
  12. <DispStreetView>true</DispStreetView>
  13. <UserId>271111</UserId>
  14. <PropertyId>1234</PropertyId>
  15. </LandLord>'
  16.  
  17.  
  18. SELECT
  19. Tbl.Col.value('Address1[1]', 'varchar(100)') AS address1,
  20. Tbl.Col.value('City[1]', 'varchar(100)') AS City,
  21. Tbl.Col.value('County[1]', 'varchar(100)') AS County,
  22. Tbl.Col.value('State[1]', 'varchar(100)') AS State,
  23. Tbl.Col.value('DispStreetView[1]', 'bit') AS DisplayStreetView,
  24. Tbl.Col.value('UserId[1]', 'int') AS UserId
  25. FROM @xml.nodes('//LandLord') Tbl(Col)
  26.  
  27.  
  28. -- SECOND WAY
  29.  
  30.  
  31. DECLARE @idoc int;
  32. DECLARE @doc varchar(1000);
  33. SET @doc ='
  34. <ROOT>
  35. <Customer CustomerID="VINET" ContactName="Paul Henriot">
  36. <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
  37. <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
  38. <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
  39. </Order>
  40. </Customer>
  41. <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
  42. <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
  43. <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
  44. </Order>
  45. </Customer>
  46. </ROOT>'
  47. --Create an internal representation of the XML document.
  48. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
  49. -- Execute a SELECT statement that uses the OPENXML rowset provider.
  50. SELECT *
  51. FROM OPENXML (@idoc, '/ROOT/Customer',1)
  52. WITH (CustomerID varchar(10),
  53. ContactName varchar(20))
  54.  
  55. EXEC sp_xml_removedocument @idoc

Report this snippet  

You need to login to post a comment.