What is OPENXML in SQL Server?

Answer:

OPENXML can parse the xml data in SQL server very efficiently in SQL Server. OpenXML primarily gives the ability to insert XML data to the relational database, however we can query the data too using OpenXML. We need to specify the path of the xml element using xpath.

Syntax:

DECLARE @index int

DECLARE @xmlString varchar(8000)

SET @xmlString ='<Persons>

    <Person id="1">

     <Name>Mohan</Name>

     <PhoneNo>34343</PhoneNo>

    </Person>

    <Person id="2">

     <Name>Sita</Name>

     <PhoneNo>23432</PhoneNo>

    </Person>

</Persons>'

 

EXEC sp_xml_preparedocument @index OUTPUT, @xmlString 

 

SELECT *

FROM OPENXML (@index, 'Persons/Person')

WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')

 

EXEC sp_xml_removedocument @index

The above code snippet will give following result.
---------------------------------
1 Mohan 34343
2 Sita 23432
---------------------------------