SQL Server Operations XML example

SQL Server Operations XML example

/*Getting Started with SQL xml:--by jinjazz--http://blog.csdn.net/jinjazz 1, xml: Can recognize elements, attributes and Values 2, XPath: addressing language, Similar to the Windows directory lookup (not used by the dir command to go to wall) syntax format, these grammars can be combined as a condition: "." 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, you can combine calculations: 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 representation                Node exists 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 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&Gt;</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)

Another example

DECLARE @idoc int;DECLARE @docXML;SELECT @doc='<Root> <Item> <ID>1</ID> <Name>jinjazz</Name> <Caption> Scissors </capt ion> </Item> <Item> <ID>2</ID> <Name>zswang</Name> <Caption> Companion Water < /caption> </Item></Root>' EXECsp_xml_preparedocument@IdocOUTPUT,@docSELECT *  into#Temp  fromOPENXML (@Idoc,'/root/item',2)    with (            [ID] varchar(Ten)           ,[Name]varchar(Ten)           ,[Caption]varchar(Ten)         )Select *  from#TempDrop Table#Temp

SQL Server Operations XML example