SQL XML Process

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&Gt;</book></bookstore> '--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)

SQL XML Process