SQLServerXML query 18 sentence getting started tutorial

Source: Internet
Author: User
Tags xquery
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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.