Follow by Email

Wednesday, July 10, 2013

using OpenXML in sql server | Example of OpenXML in Sql Server | Reading XML Text using sp_xml_preparedocument

OpenXML in sql server is used to convert a XML Document in to a Sql table.

Below is the Example :

DECLARE @XMLDoc INT
DECLARE @xmlStr VARCHAR(1000)

SET @xmlStr = '<Root> <A><Name>James</Name><Mobile>123456</Mobile></A>
                      <A><Name>Rocky</Name><Mobile>789123</Mobile></A></Root>'

EXEC sp_xml_preparedocument @XMLDoc OUTPUT,
 @xmlStr

SELECT *
FROM OPENXML(@XMLDoc, '/Root/A', 2) WITH (
  NAME VARCHAR(10),
  Mobile VARCHAR(10)
  )

EXEC sp_xml_removedocument @XMLDoc



Output: