Getting Started with SQL XML: XPath and XQuery

/*
SQLGetting Started with XML:
--by Jinjazz
--http://blog.csdn.net/jinjazz

1. xml: Ability to recognize elements, attributes, and values

2. XPath: addressing language, lookup similar to Windows directory (go to the wall if you don't use the dir command)

Syntax format, which can be combined as a condition:
"." Express oneself, "..." Said the father, "/" said the Son, "//" to indicate the offspring,
"Name" means find by First name, "@name" means find by property

Set [condition] represents a subset of the collection based on the condition, which can be
Value: Number, Last (), last ()-number, etc.
Boolean value: Position () < number, @name = ' condition ', name= ' condition '
When the condition is a Boolean value, it can be combined: and OR

3, XQuery: Based on the XPath standard quasi-query language,SQLServer XQuery contains the following functions
exist (XPath condition): Returns a Boolean value indicating whether the node exists
Query (XPath condition): Returns a new XML document made up of qualified nodes
Value (XPath condition, data type): Returns the specified scalar value, the XPath condition result must be unique
Nodes (XPath condition): Returns the result table of a row of rows that consist of qualified nodes
*/

DECLARE @ Data XML
SET @ data = '
<bookstore>
<book category= "Cooking" >
<title lang= "en" >everyday italian</title>
<author>giada De laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category= "CHILDREN" >
<title lang= "JP" >harry potter</title>
<author>j K. rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category= "WEB" >
<title lang= "en" >xquery Kick start</title>
<author>james mcgovern</author>
<author>per bothner</author>
<author>kurt cagle</author>
<author>james linn</author>
<author>vaidyanathan nagarajan</author>
<year>2003</year>
<price>49.99</price>
</book>
<book category= "WEB" >
<title lang= "cn" >learning xml</title>
<author>erik T. ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>
'

--Test statements, if you do not understand the syntax, refer to the XPath rules above and the XQuery function description

--1, Documentation
SELECT @ Data
--2, any level presence of the price node
SELECT @ data. exist ('//price ')
--3, get all book nodes
SELECT @ data. Query ('//book ')
--4, get all nodes that contain the lang attribute
SELECT @ data. Query ('//*[@lang] ')
--5, getting the first book node
SELECT @ data. Query ('//book[1] ')
--6, get top two book nodes
SELECT @ data. Query ('//book[position () <=2] ')
--7, get last book node
SELECT @ data. Query ('//book[last ()] ')
--8, get all book nodes for price>35
SELECT @ data. Query ('//book[price>35] ')
--9, get all book nodes for category= "WEB"
SELECT @ data. Query ('//book[@category = WEB '] ')
--10, get all book nodes of lang= "en" for title
SELECT @ data. Query ('//book/title[@lang = ' en '] ')
--11, get title of lang= "en" and price>35 all book nodes
SELECT @ data. Query ('//book[./title[@lang = ' en '] or price>35] ')
--12, get the title of the Lang= "en" and price>35 the first book of the (first) title
SELECT @ data. Query ('//book[./title[@lang = ' en '] and price>35] '. Value (' (Book/title) [1] ', ' varchar (max) ')
--13, equivalent to 10
SELECT @ data. Value (' (//book[./title[@lang = "en"] and price>35]/title) [1] ', ' varchar (max) ')
--14, get the title of the Lang= "en" and price>35 first book of the (first) title of the lang attribute
SELECT @ data. Value ((//book[@category = "WEB" and price>35]/title) [1]/@lang) [1] ', ' varchar (max) ')
--15, get the title of the first book
Select Tab. Col. Value (' (Book/title) [1] ', ' varchar (max) ') as Title
from @ data. Nodes (' bookstore ') as Tab (Col)
--16, get the first author of each book
Select Tab. Col. Value (' author[1] ', ' varchar (max) ') as Title
from @ data. Nodes ('//book ') as Tab (Col)
--17, get all the information for all book
Select
T. C. Value (' title[1] ', ' varchar (max) ') as title,
T. C. Value (' year[1] ', ' int ') as year,
T. C. Value (' title[1] ', ' varchar (max) ') as title,
T. C. Value (' price[1] ', ' float ') as Price,
T. C. Value (' author[1] ', ' varchar (max) ') as Author1,
T. C. Value (' author[2] ', ' varchar (max) ') as Author2,
T. C. Value (' author[3] ', ' varchar (max) ') as Author3,
T. C. Value (' author[4] ', ' varchar (max) ') as Author4
from @ data. Nodes ('//book ') as T (C)
--18, get all the information for a book that is not Japanese (lang!= "JP") and is more than 35
Select
T. C. Value (' title[1] ', ' varchar (max) ') as title,
T. C. Value (' year[1] ', ' int ') as year,
T. C. Value (' title[1] ', ' varchar (max) ') as title,
T. C. Value (' price[1] ', ' float ') as Price,
T. C. Value (' author[1] ', ' varchar (max) ') as Author1,
T. C. Value (' author[2] ', ' varchar (max) ') as Author2,
T. C. Value (' author[3] ', ' varchar (max) ') as Author3,
T. C. Value (' author[4] ', ' varchar (max) ') as Author4
from @ data. Nodes ('//book[./title[@lang!= "JP"] and price>35] "as T (C)