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]()