Copy CodeThe code is as follows:
/*01.
Introduction to 02.sql XML:
--by Jinjazz
--http://blog.csdn.net/jinjazz
25l
06.1, XML: Ability to recognize elements, attributes, and values
07.
08.2, XPath: Addressing language, lookup similar to Windows directory (go to the wall if you don't use the dir command)
09.
10. Syntax format, which can be combined as a condition:
11. "." Express oneself, "..." Said the father, "/" said the Son, "//" to indicate the offspring,
"Name" means find by First name, "@name" means find by property
13.
14. "Set [Condition]" indicates a subset of the collection based on the condition, which can be
15. Value: Number, Last (), last ()-number, etc.
16. Boolean value: Position () < number, @name = ' condition ', name= ' condition '
17. When the condition is a Boolean value can be combined: and OR
18.
19.3, XQuery: A quasi-query language based on an XPath label, SQL Server XQuery contains the following functions
exist (XPath condition): Returns a Boolean value indicating whether a 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 consisting of qualified nodes
24.*/
Copy CodeThe code is as follows:
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 12
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)
