.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

SQL Server - Reading XML Data

Posted By:Lakhan Pal Garg       Posted Date: September 12, 2009    Points: 10    Category: SQL Query    URL: http://www.dotnetspark.com  
 

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


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, element
centric etc. for more details about this
Visit: http://msdn.microsoft.com/en-us/library/ms186918.aspx
***********************************************************************
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/

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend