SQLServer XML query Quick Start (18 sentences)

SQL xml entry: -- by 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)