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

DECLARE @h int

DECLARE @xmldoc VARCHAR(1000)
--xmldoc is set with the xml elements which are to be inserted into the table students with FirstName,ID,Technology as table columns

SET @xmldoc =
'<root>
<student FirstName="Ravi" ID="1" Technology="DotNet"></student>
<student FirstName="Avdesh" ID="2" Technology="DotNet"></student> </root>'

EXEC sp_xml_preparedocument @h OUTPUT, @xmldoc

--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

INSERT INTO student
SELECT * FROM OpenXML(@h,'/root/student')
WITH student
EXEC sp_xml_removedocument @h
--sp_xml_removedocument free's up the memory.

Output:


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

DECLARE @h int

DECLARE @xmldoc VARCHAR(1000)
--xmldoc is set with the xml elements which are to be inserted into the table students with FirstName,ID,Technology as table columns

SET @xmldoc =
'<root>
<student FirstName="Ravi Sharma" ID="1" Technology="DotNet"></student> <student FirstName="Avdesh" ID="2" Technology="DotNet"></student> </root>'

EXEC sp_xml_preparedocument @h OUTPUT, @xmldoc
--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

UPDATE student
SET
FirstName = x.FirstName
,ID = x.ID
,Technology = x.Technology
FROM OpenXML(@h,'/root/student')
WITH (FirstName nvarchar(20),ID nvarchar(20),Technology nvarchar(20)) x where student.ID='1'

EXEC sp_xml_removedocument @h
--sp_xml_removedocument free's up the memory.

select * from student

Output: