SQL Fetch node values in 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 syntax can be combined into a bar Pieces: "." Express Yourself, "..." Represents the father, "/" means the Son, "//" means descendants, "name" means lookup by name, "@name" means look up "set [condition]" by attribute means a subset of the collection according to the condition                Can be numeric: number, Last (), last ()-numeric Boolean value: Position () < number, @name = ' condition ', name= ' condition ' When the condition is Boolean, it can be combined: 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                Existence of query (XPath condition): Returns a new XML document consisting of eligible 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 column consisting of a qualifying node */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>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 statement, if you do not understand the syntax, refer to the above XPath rule and XQuery function description--1, document Select @data--2, Any level exists for 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, gets the first twoBook node 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 of the category= "WEB" select @data. Query (' book[@category = "WEB"]--10, gets all the book nodes of the title lang= "en" select @data. Query ('//book/title[@lang = ' en '] ')--11, Gets the title of the Lang= "en" and all the book nodes of the price>35 select @data. Query ('//book[./title[@lang = "en"] or price>35] ')--12, Get the title of lang= "en" and price>35 First book (first) Titleselect @data. Query ('//book[./title[@lang = "en"] and price>35] '). Value (' (Book/title) [1] ', ' varchar (max) ')--13, equivalent to 12select @data. Value (' (//book[./title[@lang = "en"] and price>35] /title) [1] ', ' varchar (max) ')--14, gets the title of the Lang= "en" and price>35 the 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 first book Titleselect Tab.Col.value (' (Book/title) [1] ', ' varchar (max) ') as title from @data. Nodes (' bookstore ') as Tab (Col)--16, gets the first authorsele of each bookCT 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 Author4from @data. Nodes ('//book ') as T (C)--18, get all information for books that are not Japanese (lang!= "JP") and 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 Author4from @ Data.nodes ('//book[./title[@lang! = "JP"] and price>35] ') asT (C) 

SQL Fetch node values in XML