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