SQL Server XML statements

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 XPath condition results 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> 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 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 10
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)