Parse XML Data in SQL Server 2005 without using OPENXML Method

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='<Library>
<Subject name="ASP.NET">
<Book ID="1">
<Author>Lakhan Pal Garg</Author>
<Title>ASP.NET Tips</Title>
<Book ID="2">
<Author>Lakhan Pal Garg</Author>
<Title>SQL Server Tips</Title>
<Subject name="XML">
<Book ID="3">
<Title>XSLT Tutorial</Title>
<Book ID="4">
<Title>XML Parsing in SQL Server</Title>

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.


Lakhan Pal Garg said...

Hello Friends,

some Times we got the error with the above method

INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods

To Resolve the above error just Add :

SET ARITHABORT ON in the start of your store procedure or statements.

Thanks & Regards
Lakhan Pal Garg