Sql server XQuery

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, Let us start by querying the above XML using "Query" method.

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 {data($x/title)}
else {data($x/title)}');

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

I will explain each one.

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,

This code snippet contains two statements. The first statement makes the change to the XML
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.

Deleting data is very similar. You identify the node to be deleted, which makes the change to the stored XML data structure. Assume that you wanted to remove the first item from the list. You would need to reference the item uniquely using an approach similar to the scalar query. This code provides an example.

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,