Some Important XML Data Type Methods in SQL Server 2012

Today, I have provided an article showing you Query, Node and Exist methods with XML type variables in SQL Server 2012. To use an XML method, we create a XML type variable to return data from columns and variables of the XML data type using the Query and Node methods. Let's have a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

Query Method

It is probably the simplest and most straightforward of the XML methods. The query method retrieves a subset of untyped XML from the target XML instance.

Syntax

obj.query('XQuery')

Example

First, I declared the @xml variable as type XML. The query method is then used to specify an XQuery against the document.

DECLARE @xml xml

SELECT @XML='<body>

    <AuthorName id="BK">

      <Book>SQL Server Book.</Book>

      <Book>Oracle Book.</Book>

      <Book>Csharp Programing Book. </Book>

      <Book>Java Programing Book.</Book>

      <Book>Visual Basic Book</Book>

      <Book>XML Database Book.</Book>

    </AuthorName>

  </body>

'

SELECT @xml.query('//Book[3]/text()')--text of Third element

Now hit F5 to execute it.

Query-Method-SQL-Server.jpg

Node Method

We use the XML column type's nodes() function to transform the XML into a rowset. The node function can return more than one row. You must assign a table alias and column alias to the rowset view returned by the method, as shown in the following syntax:

Syntax

nodes (XQuery) as Table(Column)

Example

DECLARE @xml xml

SELECT @XML='<body>

    <AuthorName id="BK">

      <Book>SQL Server Book.</Book>

      <Book>Oracle Book.</Book>

      <Book>Csharp Programing Book. </Book>

      <Book>Java Programing Book.</Book>

      <Book>Visual Basic Book</Book>

      <Book>XML Database Book.</Book>

    </AuthorName>

  </body>

'

SELECT M.query('.') FROM @xml.nodes('/body/AuthorName/Book/text()') as T2(M)

Now hit F5 to execute it.

Node-Method-SQL-Server.JPG

Exist Method

The exist() method lets you test for the existence of an element or one of its values. This function returns 1 for an XQuery expression when it evaluates to TRUE, otherwise it returns 0 for FALSE.

The general syntax of the exist method is:

Syntax

DbObject.exist('XQuery')

Example

DECLARE @xml xml

DECLARE @exist BIT

SELECT @XML='<body>

    <AuthorName id="BK">

      <Book>SQL Server Book.</Book>

      <Book>Oracle Book.</Book>

      <Book>Csharp Programing Book. </Book>

      <Book>Java Programing Book.</Book>

      <Book>Visual Basic Book</Book>

      <Book>XML Database Book.</Book>

    </AuthorName>

  </body>

'

Set @exist= @XML.exist('/body/AuthorName/Book/text()')

 SELECT @exist as ExistMethod

Now hit F5 to execute it.

Exist-Method-SQL-Server.jpg

Up Next
    Ebook Download
    View all
    Learn
    View all