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

Top 5 Contributors of the Month
Sandeep Singh
Melody Anderson
Eminent IT

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

 Subscribe to Code Snippets

Parse XML Data in SQL Server 2005 without using OPENXML Method

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

In SQL Server 2005 we can parse the XML Data without using OPENXML Methods that was used in SQL Server 2000.

There are few methods defined in SQL Server 2005 for XML DataType like:
  • nodes
  • query
  • value
Sample Code:
DECLARE @XmlData xml
SET @XmlData='

Lakhan Pal Garg

Lakhan Pal Garg
SQL Server Tips

XSLT Tutorial

XML Parsing in SQL Server


select R.i.value('@ID', 'varchar(30)') [BookID],
R.i.query('Author').value('.', 'varchar(30)') [Author],
R.i.query('Title').value('.', 'varchar(30)') [Title],
R.i.query('Price').value('.', 'varchar(30)') [Price]

from @XmlData.nodes('/Library/Subject/Book') R(i)

In the above Select Statement we have used @XmlData.nodes and this will return a node list we used the Alias for this "R" and i is the index of the node. now to read the value of a attribute we can use R.i.value('@ID','INT') [BookID] here BookID is Alias name for column. and to read the value of an element that is child of Book we need to write like this R.i.query('Author').value('.','varchar(30)') [AuthorName] Author is the name of Child element of Book.

Thanks & Regards
Lakhan Pal Garg


Further Readings:


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