In SQL Server, the XQuery functionality is implemented through the query() method of the xml datatype. The query method takes parameters that define the XQuery structure and the namespaces if necessary, returning XML from the expression that represents the results of the XQuery request. Let's start with samples. Declare and set an xml type in sql query window as shown in the image below,

Predicates
XQuery uses predicates to limit the extracted data from XML documents.
The following predicate is used to select all book elements where author is "J K. Rowling"
select @xmlBookStore.query('/bookstore/book[author="J K. Rowling"]'); The output would be,

So with the above query we filtered xml based an a condition.
If we want select only one element based an a condition, for example if i want to select all title elements where price of book greater than or equal zero then the XQuery would be,
select @xmlBookStore.query('/bookstore/book[price>=30]/title');
output would be as below

FLWOR:
Just as a SQL query has a SELECT, FROM, and WHERE clause, XQuery also has distinct sections that do specific tasks. The W3C has defined the following structure for XQuery expressions using the abbreviation FLWOR (pronounced “flower”). It stands for:
for: sets the context
let: sets the variables
where: sets the conditions
order by: sets the sort
return: sets the response
The following FLWOR expression will select exactly the same as the path expression above:
select @xmlBookStore.query('for $x in /bookstore/book
where $x/price>=30
return $x/title');
Output would be the same as above .
With FLWOR you can sort the result:
If i want to get Books where price of book greater than or equal to 30 and want to order by title descending then query should be,
SELECT @xmlBookStore.query(
'for $x in /bookstore/book
where $x/price>=30
order by ($x/title)[1] descending
return $x'
"If-Then-Else" expressions are allowed in XQuery.
Look at the following example:
SELECT @xmlBookStore.query(
'for $x in /bookstore/book
return if ($x/@category="CHILDREN")
then
else
In the above example am comparing the Category attribute and if condition matches it writes Child element else it writes adult element.
Output is,
Other XML Datatype Methods
In addition to the query() method and the xml datatype, there are other methods that you can use to explore and manipulate XML data. These methods are
value() Returns a scalar value from an XML document
exist() Returns a value indicating if the PATH node exists in a document
modify() Provides for modification of an XML document through the XQuery modification extensions
nodes() Provides a mechanism for shredding an XML document to a relational structure
Retrieving Scalar Values From XML
The value() method uses an XPath statement to return the value of a particular point in the XML structure. As its name implies, it returns a single scalar value. suppose you wanted to retrieve the value of the price field for the first items in the document. Using the value() method, the code would look like this:
SELECT @xmlBookStore.value(
'(/bookstore/book/price)[1]', 'decimal'
) AS Price;
Value() has two parameters, one represents the XPath and the second parameter represents the type. In our xml Price is decimal values. In this example, the XPath statement must guarantee that it returns on a single value. The purpose of the [1] in the code is to ensure that this takes place.
The XQuery DML extensions have three supported operations:
a. insert
b. delete
Suppose that you wanted to add another item to your items list. This would be an insert operation and could be done dynamically by using this code:

output would be as below,

data structure stored in the variable by adding another item as the last item in the list. You could also specify where to add the new data: first, before, or after the referenced node. The second statement is a simple SELECT that will give you the results of the modification.
SET @xmlBookStore.modify(
'delete (//bookstore/book)[1]')
SELECT @xmlBookStore;
In the above query we deleted the First book elements in the document. Output would be as below,
No comments:
Post a Comment