What is OpenXML

Advantages of Using OPENXML

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.
Output
outputOpenXml.bmp
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
Output
outputOPenXmlUpd.bmp