Read XML Data in SQL Server 2005

Lakhangarg
Posted by Lakhangarg under Sql Server category on | Views : 2530
DECLARE @XmlHandle int
DECLARE @XMLDATA nTEXT
EXEC sp_xml_preparedocument @XmlHandle output,@XMLDATA
SELECT * FROM OPENXML (@XmlHandle, '{XPath}',{flag})
WITH ([col1] int '@xmlAttribute',[col2] VARCHAR(100) '.XMlLElement')
EXEC sp_xml_removedocument @XmlHandle


in the above Sample @XMLDATA is the XML Data that needs to be read.

sp_xml_preparedocument - Reads the XML text provided as input (@XMLDATA) ,
the xml text and returns a handle that can be used to access the newly created internal representation of the XML document. The output of this procedure is XML handle that is
used for OPENXML command.

sp_xml_removedocument - A parsed document is stored in the internal cache of
SQL Server, so sp_xml_removedocument is used to remove the xml document.

OPENXML - OPENXML provides a rowset view over an XML document. Because
OPENXML is a rowset provider, OPENXML can be used in Transact-SQL
statements in which rowset providers such as a table, view, or the
OPENROWSET function can appear.

The last Attribute is for flag means weather it is attribute centric

Example:
***********************************************************************
DECLARE @xmlDoc VARCHAR(8000)
DECLARE @xmlHandle INT
BEGIN
SET @xmlDoc ='<Products><Product ID="1"><Name>Sugar</Name><Price>30 rs/Kg</Price>
</Product><Product ID="2"><Name>Milk</Name><Price>22 rs/Kg</Price></Product></Products>'

EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlDoc
SELECT * FROM OPENXML (@xmlHandle, '//Product', 2) WITH
(
ID INT '@ID',
[Name] VARCHAR(20) 'Name',
Price VARCHAR(10) 'Price'
)
EXEC sp_xml_removedocument @xmlHandle
END

Output:
ID Name Price
1 Sugar 30 rs/Kg
2 Milk 22 rs/

Comments or Responses

Login to post response