1.xml.exist
Enter an XQuery expression that returns either 0,1 or null. 0 means not present, 1 indicates existence, NULL indicates NULL input
2.xml.value
Enter an XQuery expression that returns a SQL Server scalar value
3.xml.query
Enter an XQuery expression that returns a SQL Server XML type stream
4.xml.nodes
Enter an XQuery expression that returns a set of rows for an XML-formatted document
5.xml.modify
Insert, update, and delete operations on XML nodes using XQuery expressions.
The following examples illustrate the above five operations:
DECLARE @XMLVar XML = '
<catalog>
<book category= "ITPro" >
<title>windows Step by step</title>
<author>bill zack</author>
<price>49.99</price>
</book>
<book category= "Developer" >
<title>developing ADO .net</title>
<author>andrew brust</author>
<price>39.93</price>
</book>
<book category= "ITPro" >
<title>windows Cluster server</title>
<author>stephen forte</author>
<price>59.99</price>
</book>
</catalog> '
1. Xml.exist
Select @XMLVar. exist ('/catalog/book ')-----return 1
Select @XMLVar. exist ('/catalog/book/@category ')-----return 1
Select @XMLVar. exist ('/catalog/book1 ')-----return 0
Set @XMLVar = null
Select @XMLVar. exist ('/catalog/book ')-----return null
2.xml.value
Select @XMLVar. Value ('/catalog[1]/book[1] ', ' varchar (MAX) ')
Select @XMLVar. Value ('/catalog[1]/book[2]/@category ', ' varchar (MAX) ')
Select @XMLVar. Value ('/catalog[2]/book[1] ', ' varchar (MAX) ')
The result set is:
Windows Step by Stepbill Zack49.99 Developer NULL
3.xml.query
Select @XMLVar. Query ('/catalog[1]/book ')
Select @XMLVar. Query ('/catalog[1]/book[1] ')
Select @XMLVar. Query ('/catalog[1]/book[2]/author ')
The result set is:
<book category= "ITPro" >
<title>windows Step by step</title>
<author>bill zack</author>
<price>49.99</price>
</book>
<book category= "Developer" >
<title>developing ADO .net</title>
<author>andrew brust</author>
<price>39.93</price>
</book>
<book category= "ITPro" >
<title>windows Cluster server</title>
<author>stephen forte</author>
<price>59.99</price>
</book>
<book category= "ITPro" >
<title>windows Step by step</title>
<author>bill zack</author>
<price>49.99</price>
</book>
<author>andrew brust</author>
4.xml.nodes
Select T.c.query ('. ') as result from @XMLVar. Nodes ('/catalog/book ') as T (c)
Select T.c.query (' title ') as result from @XMLVar. Nodes ('/catalog/book ') as T (c)
The result set is:
<book category= "ITPro" ><title>windows step by Step</title><author>bill .....
<book category= "Developer" ><title>developing ADO ... net</title><author>andrew .....
<book category= "ITPro" ><title>windows Cluster server</title><author>stephen .....
<title>windows Step by step</title>
<title>developing ADO .net</title>
<title>windows Cluster server</title>
5.xml.modify
See the next article for modify content.
