18 sentences for SQL Server XML query

Copy codeThe Code is as follows:
/* 01.
02. SQL xml entry:
03. -- by jinjazz
04. -- http://blog.csdn.net/jinjazz
05.
06. 1. xml: Recognition of elements, attributes, and values
07.
. Xpath: Addressing language, similar to windows Directory Search (if you have never used the dir command, go to the wall)
09.
10. Syntax format. These syntaxes can be combined into conditions:
11. "." indicates oneself, "..." indicates father, "/" indicates son, "//" indicates descendant,
12. "name" indicates search by name, and "@ name" indicates search by attribute.
13.
14. "Set [condition]" indicates that the subset of the set is obtained based on the condition. The condition can be
15. number value: Number, last (), last ()-number, etc.
16. boolean value: position () <number, @ name = 'condition', name = 'condition'
17. When the condition is a Boolean value, it can be combined for calculation: and or
18.
19. 3. xquery: the quasi-query language based on the xpath standard. sqlserver xquery contains the following functions:
20. exist (xpath condition): returns a Boolean value indicating whether a node exists.
21. query (xpath condition): returns a New xml document consisting of qualified nodes.
22. value (xpath condition, data type): returns the specified scalar value. The result of the xpath condition must be unique.
23. nodes (xpath condition): returns the result table consisting of one row and one column 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> vaidyan#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 statement. If you do not understand the syntax, refer to the above xpath rules and the description of the xquery function.

-- 1. Document
Select @ data
-- 2. Whether a price node exists at any level
Select @ data. exist ('// price ')
-- 3. Get all book nodes
Select @ data. query ('// Book ')
-- 4. Obtain all nodes that contain the lang attribute
Select @ data. query ('// * [@ lang]')
-- 5. Get the first book Node
Select @ data. query ('// book [1]')
-- 6. Get the first two book nodes
Select @ data. query ('// book [position () <= 2]')
-- 7. Get the last book Node
Select @ data. query ('// book [last ()]')
-- 8. Get all book nodes with price> 35
Select @ data. query ('// book [price> 35]')
-- 9. Get all book nodes of category = "WEB"
Select @ data. query ('// book [@ category = "WEB"]')
-- 10. Obtain all book nodes of the title lang = "en"
Select @ data. query ('// book/title [@ lang = "en"]')
-- 11. All book nodes that obtain title lang = "en" and price> 35
Select @ data. query ('// book [./title [@ lang = "en"] or price> 35]')
-- 12. Obtain the (first) title of the first book of lang = "en" and price> 35
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. Obtain the lang attribute of the First book (first) title of the title of lang = "en" and price> 35
Select @ data. value ('(// book [@ category = "WEB" and price> 35]/title) [1]/@ lang) [1]', 'varchar (max) ')
-- 15. Obtain 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. Obtain 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 information of all books
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. Obtain a message that is not in Japanese (lang! = "Jp") All information about books with a price greater 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)