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>
        <Price>$100</Price>
      </Book>
      <Book ID="2">
        <Author>Lakhan Pal Garg</Author>
        <Title>SQL Server Tips</Title>
        <Price>$90</Price>
      </Book>
    </Subject>
    <Subject name="XML">
      <Book ID="3">
        <Author>Peter</Author>
        <Title>XSLT Tutorial</Title>
        <Price>$140</Price>
      </Book>
      <Book ID="4">
        <Author>Rihana</Author>
        <Title>XML Parsing in SQL Server</Title>
        <Price>$120</Price>
      </Book>
    </Subject>
  </Library>'

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.

2 comments:

.n. said...

Hey Lakhanpal

Have you worked in Sebiz? I knew one Lakhanpal.. and wanted to know - if u'r the same.

.n. said...

hey.. u'r the same Lakhanpal... Kaatskill mountain club.

how have you been dude. write to me at my gmail id ..... nitin.gupta80

Post a Comment