SQL Operations XML

/* Get started with SQL XML:

1. xml: Can recognize elements, attributes and values

2, XPath: addressing language, similar to the Windows directory lookup (not used the dir command to go to wall) syntax format, these grammars can be combined as a condition: "." Express Yourself, "..." Indicates the father, "/" for the Son, "//" for Descendants, "name" for the name, "@name" means to find "set [condition]" by attribute means to take a subset of the set according to the condition, the condition can be numeric: number, Last (), last ()-number Boolean value: Position () < number, @name = ' condition ', name= ' condition is a Boolean value when you can consolidate: and OR

3. XQuery: Based on the XPath-labeled quasi-query language, SQL Server XQuery contains the following function exist (XPath condition): Returns a Boolean value indicating whether the node exists with a query (XPath condition) : Returns a new XML document that consists of qualifying 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 consisting of eligible 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" >learningxml</title>   <author>erik T. ray</author>   <year>2003</year>   < price>39.95</price> </book> </bookstore> ' select @data. Query ('//book/title[@lang = "en"])

Select @data. Query ('//book[title/@lang = "en"])--The test statement, if you do not understand the syntax, refer to the above XPath rule and the XQuery function description

--1, document Select @data

--2, any level exists whether the price node select @data. exist ('//price ')

--3, gets all book node select @data. Query ('//book ')

--4, gets all nodes that contain the lang attribute select @data. Query ('//*[@lang] ')

--5, gets the first book node of select @data. Query ('//book[1] ')

--6, get the top two book nodes select @data. Query ('//book[position () <=2] ')

--7, gets the last book node select @data. Query ('//book[last ()] ')

--8, gets all the book nodes of the price>35 select @data. Query ('//book[price>35] ')

--9, gets all the book nodes (and the number) of the category= "Web" select @data. Query ('//book[@category = ' web '] ') Select @data. Value (' Count (//book[ @category = "WEB"]) ', ' int ')

--10, get the title of lang= "en" For all book nodes select @data. Query ('//book[title/@lang = "en"])

--11, gets the title of the Lang= "en" of all the title nodes of select @data. Query ('//book/title[@lang = "en"])

--12, get the title of lang= "EN" and all the book nodes of price>35 Select @data. Query ('//book[./title[@lang = ' en '] or price>35] ')

--13, get the title of lang= "en" and price>35 First book (first) title Select @data. Query ('//book[./title[@lang = "en"] and price> Value (' (Book/title) [1] ', ' varchar (max) ')

--14, equivalent to @data. Value (' (//book[./title[@lang = "en"] and price>35]/title) [1] ', ' varchar (max) ')

--15, gets the title of lang= "en" and Price>35 's 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) ')

--16, 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)

--17, get the first author of each book Select Tab.Col.value (' author[1] ', ' varchar (max) ') as title from @data. Nodes ('//book ') as Tab (Col)

--18, 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.va Lue (' author[4] ', ' varchar (max) ') as Author4 from @data. Nodes ('//book ') as T (C)

--19, get all the information for a book that is not Japanese (lang!= "JP") and the price is 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)

SQL Operations XML