Query XML Fields

Find a Summary of the Well-summarized knowledge points and record them in your blog for ease of query.

/* SQL XML:      --by jinjazz      --http://blog.csdn.net/jinjazz            1. xml: Recognition of elements, attributes, and values            2. XPath: Addressing language, similar to Windows Directory Search (go to the wall without using the Dir command)                                    Syntax format. These syntaxes can be combined into conditions:                  "." Represents yourself, ".." Indicates the father, "/" Son, "//" Represent future generations,                  "name" Indicates searching by name, "@name" Search by attribute                                    "Set [conditions]"  Indicates that the subset of the set is obtained according to the condition. The condition can be                      Number value: Number, last (), last ()-Numbers, etc.                      Boolean value: Position () <number ,@ name = 'Condition' , name = 'Condition'                  When the condition is a Boolean value, it can be combined for calculation: and  or            3. XQuery: quasi-query language based on the XPath standard. sqlserver XQuery contains the following functions:                  Exist (XPath condition): returns a Boolean value indicating whether a node exists.                  Query (XPath condition): returns a New XML document consisting of qualified nodes.                  Value (XPath condition, data type): returns the specified scalar value. The result of the XPath condition must be unique.                  Nodes (XPath condition): returns a result table consisting of one row and one column of qualified 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">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 rules and the description of the XQuery function.  -- 1. Document select  @data -- 2. Whether a price node exists at any level select  @data.exist( ‘//price‘ ) -- 3. Get all book nodes select  @data.query( ‘//book‘ ) -- 4. Obtain all nodes that contain the lang attribute select  @data.query( ‘//*[@lang]‘ )  -- 5. Get the first book Node select  @data.query( ‘//book[1]‘ ) -- 6. Get the first two book nodes select  @data.query( ‘//book[position()<=2]‘ ) -- 7. Get the last book Node select  @data.query( ‘//book[last()]‘ ) -- 8. Get all book nodes with price> 35 select  @data.query( ‘//book[price>35]‘ ) -- 9. Get all book nodes of Category = "Web" select  @data.query( ‘//book[@category="WEB"]‘ ) -- 10. Obtain all book nodes of the title lang = "en" select  @data.query( ‘//book/title[@lang="en"]‘ ) -- 11. All book nodes that obtain title lang = "en" and price> 35 select  @data.query( ‘//book[./title[@lang="en"] or price>35 ]‘ ) -- 12. Obtain the (first) Title of the first book of lang = "en" and price> 35 select  @data.query( ‘//book[./title[@lang="en"] and price>35 ]‘ ).value( ‘(book/title)[1]‘ , ‘varchar(max)‘ ) -- 13. equivalent to 12 select  @data.value( ‘(//book[./title[@lang="en"] and price>35 ]/title)[1]‘ , ‘varchar(max)‘ ) -- 14. Obtain the lang attribute of the First Book (first) Title of the title of lang = "en" and price> 35 select  @data.value( ‘((//book[@category="WEB" and price>35 ]/title)[1]/@lang)[1]‘ , ‘varchar(max)‘ ) -- 15. Obtain the title of the first book select  Tab.Col.value( ‘(book/title)[1]‘ , ‘varchar(max)‘ )  as  title      from  @data.nodes( ‘bookstore‘ ) as  Tab(Col)  -- 16. Obtain the first author of each book select  Tab.Col.value( ‘author[1]‘ , ‘varchar(max)‘ )  as  title      from  @data.nodes( ‘//book‘ ) as  Tab(Col) -- 17. Get all information of all books 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‘ )  as  T(C) -- 18. Obtain a message that is not in Japanese (Lang! = "JP") All information about books with a 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  author4 from  @data.nodes( ‘//book[./title[@lang!="jp"] and price>35 ]‘ )  as  T(C)

XML fields can play a huge role in database design. For example, they are data objects of the same class but different from each other, for example, for different types of documents of the previous Customs project, the records of the engine scanning objects, and so on, a common data table that is used together can be stored in different data structures using XML fields, different parts of different data objects can be stored in XML fields, and the same parts can be abstracted and stored in normal fields. This design can simplify database tables, it can also meet the query needs of different data objects. I feel that I can try this method more in the future.

Query XML Fields