18 small instances getting started SQL Server XML query

/* SQL XML entry: -- By erichu -- http://blog.csdn.net/chinahuyong 1, XML: Can Understand elements, attributes and values 2, XPath: Addressing language, similar to the Windows Directory Search syntax format (if the Dir command is not used, go to the wall), these syntaxes can be combined as conditions :". "represents yourself ,".. "indicates father,"/"indicates son," // "indicates descendant," name "indicates search by name, "@ name" indicates searching by attribute "set [condition]" indicates obtaining a subset of a set based on the condition. The condition can be a number value: Number, last (), last () -boolean values such as numbers: Position () <number, @ name = 'condition', name = 'condition' can be combined for calculation when the condition is Boolean: And or 3, XQuery: based on the quasi-query language of the XPath standard, sqlserver XQuery includes the following function exist (XPath condition): returns a Boolean value to indicate whether the node has a Qu Ery (XPath condition): returns the New XML document value (XPath condition, data type) composed of qualified nodes. It returns the specified scalar value, the result of the XPath condition must be unique ): returns the result table with one row and one column composed of qualified nodes */declare @ data xmlset @ 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 XQuery function description -- 1. Select @ data -- 2. whether the price node exists at any level select @ data. exist ('// price') -- 3. Obtain all book nodes select @ data. query ('// Book') -- 4. Obtain all nodes that contain the lang attribute select @ data. query ('// * [@ Lang]') -- 5. Obtain the first book node select @ data. query ('// book [1]') -- 6. Obtain the first two book nodes select @ data. query ('// book [position () <= 2]') -- 7. Obtain the last book node select @ data. query ('// B Ook [last ()] ') -- 8. Obtain all book nodes of price> 35 select @ data. query ('// book [price> 35]') -- 9. Obtain all book nodes of Category = "Web" select @ data. query ('// book [@ Category = "Web"]') -- 10. Select @ data for all book nodes that obtain the title lang = "en. query ('// book/title [@ lang = "en"]') -- 11. Select @ data for all book nodes that obtain title lang = "en" and price> 35. query ('// book [. /title [@ lang = "en"] or price> 35] ') -- 12. For the first book that gets the title lang = "en" and the price> 35 (first) titleselect @ 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 titleselect tab of the first book. col. value ('(book/Title) [1]', 'varchar (MA X) ') as title from @ data. nodes ('bookstore') as tab (COL) -- 16. Obtain the first authorselect tab of each book. col. value ('author [1] ', 'varchar (max)') as title from @ data. nodes ('// Book') as tab (COL) -- 17. obtain all information about 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 author4from @ data. nodes ('// Book') as T (c) -- 18. Obtain a name that is not Japanese (Lang! = "JP") 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 author4from @ d ATA. nodes ('// book [./title [@ Lang! = "JP"] and price> 35] ') as T (c)