Openxml - XML Integration with SQL Server

What is OpenXML

  • SQL Server 2000 provides a system-defined function, OpenXML, that creates Rowsets from XML documents.
  • OPENXML allows the data in XML document to be treated just like the columns and rows of your database table
  • The OPENXML function allows developers to parse XML data so that it can be stored as relational data in tabular form. This function supports the XML data type and the sp_xml_preparedocument system stored procedure accepts this new data type. This procedure is used by OPENXML function. Whereas SQL Server 2000 allowed the use of varchar, nvarchar, text or ntext variables to generate a document handle using the abovementioned stored procedure, SQL Server 2005 allows developer to use the xml variable additionally.

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.

OPENXML limitations

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.
Example 1- Inserting records from XMLDoc to sql table
  1. DECLARE @h int  
  2. DECLARE @xmldoc VARCHAR(1000)   
  3. --xmldoc is set with the xml elements which are to be inserted into the table students with FirstName,ID,Technology as table columns  
  5. SET @xmldoc =  
  6. '<root>   
  7. <student FirstName="Ravi" ID="1" Technology="DotNet"></student>   
  8. <student FirstName="Avdesh" ID="2" Technology="DotNet"></student> </root>'  
  10. EXEC sp_xml_preparedocument @h OUTPUT, @xmldoc  
  12. --This sp_xml_preparedocument is internal server SP (pseudo SP). which takes the xmldoc as input and gives an output in @h which contains the data which is to be manipulated further  
  14. INSERT INTO student   
  15. SELECT * FROM OpenXML(@h,'/root/student')   
  16. WITH student   
  17. EXEC sp_xml_removedocument @h   
  18. --sp_xml_removedocument free's up the memory.   
sp_xml_preparedocument can only process text or untyped XML. If an instance value to be used as input is already typed XML, first cast it to a new untyped XML instance or as a string and then pass that value as input.
A parsed document is stored in the internal cache of SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory. 
Example 2 - Updating records from XMLDoc to sql table
  1. DECLARE @h int  
  3. DECLARE @xmldoc VARCHAR(1000)   
  4. --xmldoc is set with the xml elements which are to be inserted into the table students with FirstName,ID,Technology as table columns  
  6. SET @xmldoc =   
  7. '<root>   
  8. <student FirstName="Ravi Sharma" ID="1" Technology="DotNet"></student> <student FirstName="Avdesh" ID="2" Technology="DotNet"></student> </root>'  
  10. EXEC sp_xml_preparedocument @h OUTPUT, @xmldoc   
  11. --This sp_xml_preparedocument is internal server SP (pseudo SP). which takes the xmldoc as input and gives an output in @h which containd the data which is to be manipulated further  
  13. UPDATE student   
  14. SET   
  15. FirstName = x.FirstName   
  16. ,ID = x.ID   
  17. ,Technology = x.Technology   
  18. FROM OpenXML(@h,'/root/student')   
  19. WITH (FirstName nvarchar(20),ID nvarchar(20),Technology nvarchar(20)) x where student.ID='1'  
  21. EXEC sp_xml_removedocument @h   
  22. --sp_xml_removedocument free's up the memory.  
  24. select * from student