Getting Started with SQL 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 the dir command to go to wall) syntax format, these grammars can be combined as the 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, etc. Boolean: Position () < number, @name = ' condition ', name= ' condition ' condition is Boolean value when you can combine calculations: and or 3, XQuery: Based on the XPath subscript of the quasi-query language, SQL Server XQuery contains the following functions exist (XPath condition): Returns a Boolean value that indicates whether the node exists with a query (XPath condition): Returns a new XML document that consists of eligible nodes value (XPath condition, data type): Returns the specified scalar value, and the XPath condition result must be unique nodes (XPath condition): Returns a table of results from one row of eligible nodes*/ Declare @dataXMLSet @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 the XQuery function description--1. DocumentationSelect @data --2. If there is a price node at any levelSelect @data. exist ('//price') --3. Get all book NodesSelect @data. Query ('//book') --4. Get all nodes that contain the lang attributeSelect @data. Query ('//*[@lang]') --5. Get the first book nodeSelect @data. Query ('//book[1]') --6. Get the first two book nodesSelect @data. Query ('//book[position () <=2]') --7. Get the last book nodeSelect @data. Query ('//book[last ()]') --8. Get all the book nodes of Price>35Select @data. Query ('//book[price>35]') --9. Get all the book nodes of category= "WEB"Select @data. Query ('//book[@category = "WEB"]') --10. Get all book nodes of the title lang= "en"Select @data. Query ('//book/title[@lang = "en"]') --11, get the title of lang= "en" and price>35 all book nodesSelect @data. Query ('//book[./title[@lang = "en"] or price>35]') --12, get the title of lang= "en" and Price>35 's first book (first) titleSelect @data. Query ('//book[./title[@lang = "en"] and price>35]'). Value ('(Book/title) [1]','varchar (max)') --13, equivalent toSelect @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 attributeSelect @data. Value ('((//book[@category = "WEB" and price>35]/title) [1]/@lang] [1]','varchar (max)') --15. Get the title of the first bookSelectTab.Col.value ('(Book/title) [1]','varchar (max)') astitle from @data. Nodes ('Bookstore') asTab (Col)--16. Get the first author of each bookSelectTab.Col.value ('Author[1]','varchar (max)') astitle from @data. Nodes ('//book') asTab (Col)--17. Get all the information of all bookSelectT.c.value ('Title[1]','varchar (max)') astitle, T.c.value ('Year[1]','int') as  Year, T.c.value ('Title[1]','varchar (max)') astitle, T.c.value ('Price[1]','float') asPrice , T.c.value ('Author[1]','varchar (max)') asAuthor1, T.c.value ('Author[2]','varchar (max)') asAuthor2, T.c.value ('Author[3]','varchar (max)') asAuthor3, T.c.value ('Author[4]','varchar (max)') asAuthor4 from @data. Nodes ('//book') asT (C)--18. Get all information for books that are not Japanese (lang!= "JP") and are priced at more than 35SelectT.c.value ('Title[1]','varchar (max)') astitle, T.c.value ('Year[1]','int') as  Year, T.c.value ('Title[1]','varchar (max)') astitle, T.c.value ('Price[1]','float') asPrice , T.c.value ('Author[1]','varchar (max)') asAuthor1, T.c.value ('Author[2]','varchar (max)') asAuthor2, T.c.value ('Author[3]','varchar (max)') asAuthor3, T.c.value ('Author[4]','varchar (max)') asAuthor4 from @data. Nodes ('//book[./title[@lang! = "JP"] and price>35]') asT (C)

Source: http://www.jb51.net/article/19659.htm

Getting Started with SQL XML