Querying and Modifying XML Data in SQL Server 2005

Lakhangarg
Posted by in Sql Server category on for Intermediate level | Views : 7799 red flag

Querying XML instances stored in an XML column requires parsing binary XML data in the column. Parsing binary XML is much faster than parsing the text form of the XML data.
Introduction
Querying XML instances stored in an XML column requires parsing binary XML data in the column. Parsing binary XML is much faster than parsing the text form of the XML data.
Methods used for Querying and Modifying XML
  • The query() method is useful for extracting parts of an XML instance.
  • The value() method extracts a scalar value from an XML instance.
  • The exist() method is useful for existential checks on an XML instance.
  • The nodes() method yields instances of a special XML data type, each of which has its context set to a different node that the XQuery expression evaluates to.
  • The modify() method permits modifying parts of an XML instance, such as adding or deleting subtrees, or replacing scalar values

Sample Code that will explain the above methods
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)


declare @xml xml
set @xml = '<root/>'
select @xml

declare @value varchar(10)
set @value = 'val1'
set @xml.modify('insert <item value="{sql:variable("@value")}" /> into (/root)[1]')
select @xml

set @value = 'val2'
set @xml.modify('replace value of (/root/item/@value)[1] with "val2"')
select @xml

set @value = 'val3'
set @xml.modify('replace value of (/root/item/@value)[1] with sql:variable("@value")')
select @xml



Page copy protected against web site content infringement by Copyscape

About the Author

Lakhangarg
Full Name: Lakhan Pal
Member Level: Silver
Member Status: Member,Moderator
Member Since: 8/17/2009 12:39:46 AM
Country: India

http://lakhangarg.blogspot.com
Hello Friends Myself Lakhan Pal Garg and i am a B.Tech (IT) Graduate and having 8+Years of Exp. in Microsoft Technology. I have Write a Blog Named Free Code Snippets (http://lakhangarg.blogspot.com/) I hope you must visit my blog as your valuable feedback will motivate me to write more and improve my mistake. If you want to gain more knowledge then share it with others.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)