SQL operation XML notes

Declare @ xmldata XML
Set @ xmldata ='
<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> vaidyan#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>

 

 

1. Query: @ XML. Query (XPath) XPath is a matching string. Must be a complete, non-concatenated string. '.' Indicates itself '/' indicates the subnode '//' indicates the descendant '*' indicates any

Query all the book nodes whose attributes are category = "Web" in bookstore:

　　

Select @ xmldata. Query ('./bookstore/book [@ Category = "Web"]')

Query the last book node:

　　
Select @ xmldata. Query ('./bookstore/book [last ()]')

The number in the brackets indicates the number of nodes. Note: here the number 1 indicates the first node, and the non-zero indicates the first node. It can also be like this: [position () <= 2] indicates the first two nodes

Query all nodes with the lang attribute: (query all nodes with the lang attribute in all descendant nodes)

Select @ xmldata. Query ('// * [@ Lang]')

The query can contain the following conditions: Query all price nodes with a value greater than 30

 

Select @ xmldata. Query ('// price [text ()> 30]')

Or a more complex condition: Query all book nodes. The condition is that the lang attribute value of the title node of its subnode is "en" and the year node value is "2003"

 

Select @ xmldata. Query ('// book [./title [@ lang = "en"] And./year [text () = "2003"]')

Sometimes the query string may contain some parameters: the following code: we may expect to query all the book nodes whose attributes are category = "Web" using such a statement.

 

Declare @ parm varchar (3)
Set @ parm = "Web"
Select @ xmldata. Query ('// book [@ Category = "' + @ parm + '"]')

However, this is incorrect. The following error is returned: parameter 1 of the XML data type method "query" must be a string.

You can solve this problem through SQL: variable ("@ parm:

 

Select @ xmldata. Query ('// book [@ Category =SQL: Variable ("@ parm")] ')

【

 SQL: variable ("@ para ")Used to provide parameters,

HoweverSQL: column ("Columnname")It is more convenient to query directly based on the provided column name, for example:

Declare @ classxml XML
Select @ classxml = [configvalue]. Query ('./root/GAME/classs/class') from tablename where type = 'animal'

Select @ classxml. Query ('./class [@ ID =SQL: column ("class ")] '). Value ('./class [1]/@ name', 'nvarchar (50) ') as 'class ',

]

In addition, this query method also exists:

 

Select @ xmldata. Query ('for $ B in bookstore/book where $ B/year [text () = "2003"] Return ($ B )')

 

Select @ xmldata. Query ('for $ B in bookstore/book where $ B/author order by $ B/price [1] descending return ($ B )')

Note: The second line of code after the order by statement must be an exact column, so use [1]. Otherwise, if $ B has multiple price nodes, this query is not what we need

In addition to the @ XML. Query (XPath) method

. Exist (XPath) method:

Returns 0 or 1, indicating whether the content selected by XPath exists.

. Value (XPath, type ):

Returns the value of the content selected by XPath. the type of the returned value must be specified here.

To make XML queries more efficient, we can create indexes on XML data. The specific method is not recorded at the moment.

2. Modify: @ XML. Modify (insert) |. Modify (replace) |. Modify (delete)

Insert an XML segment into the specified node (Before) after | before

Set @ xmldata. Modify ('insert <Test> Hello </test> after (// book) [last ()] ')
Change the value of the price node for price = "39.95" to "50.00"

Set @ xmldata. Modify ('replace value of (/bookstore/book/price [text () = "39.95"]/text () [1] with "50.00 "')
Delete all Test Nodes

Set @ xmldata. Modify ('delete // test ')