What is OPENXML in SQL Server?

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.

Advantages of Using OPENXML

• Inserting from XML is faster when using openxml
• OPENXML provides an easy way to use an XML document as a data-source for your procedures.

Limitations with OPENXML

OPENXML is very memory intensive. The pointer returned by the system stored procedure sp_xml_preparedocument is a memory pointer to a COM XML document object model. So, you must be careful not to load very large XML documents into memory with OPENXML because it may overload your server’s memory.

Syntax:

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) 
[ WITH ( SchemaDeclaration | TableName ) ]

To avoid running out of memory, run sp_xml_removedocument to free up the memory.

Example OPENXML in SQL Server:

DECLARE @index int
DECLARE @xmlString varchar(8000)
SET @xmlString ='<Persons>
    <Person id="1">
     <Name>Dhruv</Name>
     <PhoneNo>9428665466</PhoneNo>
    </Person>
    <Person id="2">
     <Name>Ajay</Name>
     <PhoneNo>9428656213</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

OUTPUT:

Live Demo for OPENXML in Sql Server

 

Example of Update with OPENXML in SQL Server:

DECLARE @index int
DECLARE @xmlString varchar(8000)
SET @xmlString ='<Persons>
    <Person id="1">
     <Name>Pole</Name>
     <PhoneNo>9428665466</PhoneNo>
    </Person>
    <Person id="2">
     <Name>Ajay</Name>
     <PhoneNo>9428656213</PhoneNo>
    </Person>
</Persons>'
EXEC sp_xml_preparedocument @index OUTPUT, @xmlString
 
UPDATE student 
SET 
id = x.id 
,Name = x.Name 
,PhoneNo = x.PhoneNo 
FROM OPENXML (@index, 'Persons/Person')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo') x where ID='1'

EXEC sp_xml_removedocument @index