.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 >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Querying and Modifying XML Data in SQL Server 2005

Posted By:Lakhan Pal Garg       Posted Date: May 27, 2010    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

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='


Lakhan Pal Garg
ASP.NET Tips
$100


Lakhan Pal Garg
SQL Server Tips
$90




Peter
XSLT Tutorial
$140


Rihana
XML Parsing in SQL Server
$120


'

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 = ''
select @xml

declare @value varchar(10)
set @value = 'val1'
set @xml.modify('insert 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



 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

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