SQL Server - XML Methods

SQL Server - XML Methods

Below we will talk about five methods available to the XML data type in SQL Server - Query (), Value(), Exist(), Modify () and Nodes (). You can go through the examples carefully and try to understand them.

Create a new table named Library:

CREATE TABLE Library
(
  Book_ID INT PRIMARY KEY,
  Book_Info XML,
 );

Insert data into table:



Lets see what we inserted into the table:

Select * from Library


Click on the Book_Info link above to expand it and see the complete value of the column as below:



Now lets start with the methods available to XML data type in SQL Server one at a time:

Query () method
With Query () method you can specify an XQuery against an instance of the xml data type. The result is of xml type.

Syntax:
Query ('XQuery')

Where XQuery is a string () that queries for XML nodes such as elements, attributes etc. in an XML instance.

Example 1 of Query () method:

SELECT Book_Info.query('/BookInfo/BookAuthors')
from Library

Result:


Click on the link above to expand it and see the complete value of the column as below:


Example 2 of Query () method:

SELECT Book_Info.query('/BookInfo/BookCost')
from Library

Result:


Value () method
The value() method performs an XQuery against the xml and returns a scalar value.

Syntax:
value (XQuery, SQLType)

Where XQuery is XQuery expression that retrieves data from the XML instance.

SQLType cannot be an xml data type, a common language runtime (CLR) user-defined type, image, text, ntext, or sql_variant data type. SQLType can be an SQL, user-defined data type.

Example 3 of Value () method:

SELECT Book_Info.value('(/BookInfo/BookAuthors/@number) [1]', 'varchar(10)')
from Library

Result:
One

Per static typing requirements, [1] is added at the end of the path expression in the value() method to explicitly indicate that the path expression returns a singleton. [1] assures that if there are multiple elements with the same name then only the first one will be returned. It also makes sure that only one element is being referenced at a time.

Exist () method
Exist() Method returns:
1 (True), if the result is nonempty.
0 (False), if the result is empty.
NULL, if the xml data type instance against which the query was executed contains NULL.

Example 4 of Exist () method:

DECLARE @var_1 XML;
DECLARE @var_2 BIT;
SET @var_1 = (SELECT Book_Info FROM Library);
SET @var_2 = @var_1.exist('/BookInfo[YearPublished="1996"]');
SELECT @var_2;

Result:
1

Modify () method
Modify method modifies the contents of an XML document. More will be cleared by going through the examples below.

Example 5 of Modify () method:

UPDATE Library
SET Book_Info.modify('
  insert(Cost may vary depending on region)
  after(/BookInfo/BookCost)[1]')
WHERE Book_ID = 0001;

Result:


Example 6 of Modify () method:

UPDATE Library
SET Book_Info.modify('
  replace value of (/BookInfo/Comments/text())[1]
  with "Book Cost may vary depending on region" ')
WHERE Book_ID = 0001;

Result:


Example 7 of Modify () method:

UPDATE Library
SET Book_Info.modify('delete(/BookInfo/Comments)[1]')
WHERE Book_ID = 0001;

Result:


Nodes () method
Nodes() method returns a table that includes one column. You can only have one column and that column is automatically of type XML.

Syntax:
Nodes (XQuery) Table(Column)

Example 8 of Nodes () method:

DECLARE @Book_Writes XML
SET @Book_Writes =
  '
    Book_Author_B
    '
SELECT
  Category.query('./text()')
    AS BookTypes
FROM
  @Book_Writes.nodes('/BookAuthors/BookAuthor')
    AS Book(Category);

Result:


Example 9 of Nodes () method:

SELECT
  X.query('./text()')
  AS BookTypes
FROM
  Library CROSS APPLY
  Book_Info.nodes('/BookInfo/BookAuthors[@number="one"]/BookAuthor')
    AS Book(X);

Result: