A quick start with SQL Server XML queries gives you a quick understanding of SQL Server XML queries.
Quick Start with SQL Server XML query gives you a quick introduction to SQL Server XML query.
The 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 () <数字,@name='条件',name='条件'
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 .*/
The Code is as follows:
Declare @ data xml
Set @ data ='
Everyday Italian
Giada De Laurentiis
2005
30.00
Harry Potter
J k. Rowling
2005
29.99
XQuery Kick Start
James McGovern
Per Bothner
Kurt Cagle
James Linn
Vaidyan#nagarajan
2003
49.99
Learning XML
Erik T. Ray
2003
39.95
-- 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)
