Getting Started with SQL XML: XPath and XQuery

Source: Internet
Author: User
Tags sql query xpath xquery

/*
SQLGetting Started with XML:
--by Jinjazz
--http://blog.csdn.net/jinjazz

1. xml: Ability to recognize elements, attributes, and values

2. XPath: addressing language, lookup similar to Windows directory (go to the wall if you don't use the dir command)

Syntax format, which can be combined as a condition:
"." Express oneself, "..." Said the father, "/" said the Son, "//" to indicate the offspring,
"Name" means find by First name, "@name" means find by property

Set [condition] represents a subset of the collection based on the condition, which can be
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: and OR

3, XQuery: Based on the XPath standard quasi-query language,SQLServer XQuery contains the following functions
exist (XPath condition): Returns a Boolean value indicating whether the node exists
Query (XPath condition): Returns a new XML document made up of qualified nodes
Value (XPath condition, data type): Returns the specified scalar value, the XPath condition result must be unique
Nodes (XPath condition): Returns the result table of a row of rows that consist 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 statements, if you do not understand the syntax, refer to the XPath rules above and the XQuery function description

--1, Documentation
SELECT @ Data
--2, any level presence of the price node
SELECT @ data. exist ('//price ')
--3, get all book nodes
SELECT @ data. Query ('//book ')
--4, get all nodes that contain the lang attribute
SELECT @ data. Query ('//*[@lang] ')
--5, getting the first book node
SELECT @ data. Query ('//book[1] ')
--6, get top two book nodes
SELECT @ data. Query ('//book[position () <=2] ')
--7, get last book node
SELECT @ data. Query ('//book[last ()] ')
--8, get all book nodes for price>35
SELECT @ data. Query ('//book[price>35] ')
--9, get all book nodes for category= "WEB"
SELECT @ data. Query ('//book[@category = WEB '] ')
--10, get all book nodes of lang= "en" for title
SELECT @ data. Query ('//book/title[@lang = ' en '] ')
--11, get title of lang= "en" and price>35 all book nodes
SELECT @ data. Query ('//book[./title[@lang = ' en '] or price>35] ')
--12, get the title of the Lang= "en" and price>35 the first book of the (first) title
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, get the title of the Lang= "en" and price>35 first book of the (first) title of the lang attribute
SELECT @ data. Value ((//book[@category = "WEB" and price>35]/title) [1]/@lang) [1] ', ' varchar (max) ')
--15, get 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, get 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 the information for all book
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, get all the information for a book that is not Japanese (lang!= "JP") and is more 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.