Database Query XML XQuery

XQuery

The language used to extract data from XML data fields is called XQuery.

Example:

First, create a database:

Insert data into the table:

 
Insert into [xmlstudy]. [DBO]. [purchaseorders] ([purchaseid], [purchaseorder]) values (2 ,'<? XML version = "1.0" encoding = "UTF-8"?><About> <Template Name ="My style"Author ="Discuz! NT"Createdate ="2008-5-27"Ver ="2.5"Fordntver ="2.5"Copyright ="Copyright 2008 comsenz Inc."/> </About> ') Go
 

 

Insert into [xmlstudy]. [DBO]. [purchaseorders] ([purchaseid], [purchaseorder]) values (1 ,'<About><Template Name ="Beijing2008"Author ="Discuz! NT"Createdate ="2008-7-2"Ver ="2.5"Fordntver ="2.5"Copyright ="Copyright 2008 comsenz Inc."/> </About> ') Go
 

 

Next we will introduce how to use XPath expressions to extract order information.

 
Select purchaseorder. Query ('/About') As result from purchaseorders
 

 

The query result is the direct content of "about" in the purchaseorder column.

Of course, you can also query the content of the next node:

Select purchaseorder. Query ('/About/template') As result from purchaseorders
 

To further obtain specific content, you can use the following SQL statement:

 
Select purchaseorder. Query ('/About/template/author/text ()') As result from purchaseorders
 

 
No query results. Blank!
 

 
This is because the XML file must be of this type.
 

<Template Name ="Beijing2008"> <Author> discuz! NT </author> <createdate>"2008-7-2"</Createdate> <ver>"2.5"</VER> <fordntver>"2.5"</Fordntver> <copyright>"Copyright 2008 comsenz Inc."</Copyright> </template>
 

Query again:

From the preceding statement, we can see that different XPath expressions are used to obtain the content of different nodes in XML. XPath specifies the query path, where the text () function is the XPath function, returns the value contained in the XML element.

 

XQuery can also be used in combination with where !!!