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
About the Author
Full Name:
Lakhan PalMember 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.