/*
SQL XML:
-- By jinjazz
Http://blog.csdn.net/jinjazz
1. xml: Recognition of elements, attributes, and values
2. XPath: Addressing language, similar to Windows Directory Search (go to the wall without using the Dir command)
Syntax format. These syntaxes can be combined into conditions:
"." Indicates oneself, "..." indicates father, "/" indicates son, "//" indicates descendant,
"Name" indicates search by name, "@ name" indicates search by attribute
"Set [condition]" indicates that the subset of the set is obtained based on the condition. The condition can be
Number 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 for calculation: And or
3. XQuery: quasi-query language based on the XPath standard. sqlserver 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 consisting of qualified nodes.
Value (XPath condition, data type): returns the specified scalar value. The result of the XPath condition must be unique.
Nodes (XPath condition): returns a result table consisting of one row and one column 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> 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)