SQL Server XML Data Type Methods – Part 2

Hello Folks,

You would have seen in my previous article post, about the XPath and Value() XML methods. If you want to refer it again, just follow the link;

Well in this post, I would like to deal with nodes (), exist (), query (), and modify () methods.

Nodes ():

  • The nodes () method returns a row set representation of the XML document.
  • The nodes () method is useful when you want to shred an xml data type instance into relational data.
  • It also allows you to identify nodes that will be mapped into a new row.
  • Every xml data type instance has an implicitly provided context node. For the XML instance stored in a column or variable, this is the document node. The document node is the implicit node at the top of every xml data type instance.
  • The result of the nodes () method is a row-set that contains logical copies of the original XML instances.
  • You can also retrieve multiple values from the row-set by applying value () method to the row-set returned by nodes () and retrieve multiple values from the original XML instance.
  • The Syntax can be seen as:

nodes (XQuery) as Table(Column)

  • An XQuery expression operation can be performed on each node returned by the nodes () method.
  • The following example will make you clear about the nodes () method:

The following query returns an accessor to all the S_Id elements in the XML documents and applies the value () method on each node:

DECLARE @a XML
SELECT @a = '
<Students>
<S_Id>01</S_Id>
<S_Id>02</S_Id>
<S_Id>03</S_Id>
<S_Id>04</S_Id>
</Students>'
 
SELECT a.value('.','CHAR(2)') AS S_ID
FROM @a.nodes('/Students/S_Id') T(a)

The result can be seen as:

1_SQL_Server_XML_Data_Type_Methods_Part2

Now, the following example is a modified version of the preceding query that reads information from an XML column:

IF OBJECT_ID('StudXML', 'U') IS NOT NULL DROP TABLE StudXML
CREATE TABLE StudXML(S_ID INT, StudData XML)
GO
 
INSERT INTO StudXML(S_ID, StudData)
SELECT 1, '<Student S_ID = "1">
            <Subject Sub_ID = "20A" Sub_Marks = "100" />
            <Subject Sub_ID = "20B" Sub_Marks = "100" />
           </Student>'
            
INSERT INTO StudXML(S_ID, StudData)
SELECT 2, '<Student S_ID = "2">
            <Subject Sub_ID = "20C" Sub_Marks = "100" />
            <Subject Sub_ID = "20D" Sub_Marks = "100" />
           </Student>'
            
            
SELECT S_ID, a.value('@Sub_ID', 'CHAR(3)') AS Sub_Id
FROM StudXML
CROSS APPLY StudData.nodes('/Student/Subject') o(a)

The result can be seen as:

2_SQL_Server_XML_Data_Type_Methods_Part2

Note: The CROSS APPLY operator joins each node returned by the nodes () method with the table, and the value () method reads the Sub_Id value from each element returned by the nodes () method.

Exist ():

  • The exist() method checks whether an element or attribute specified by a given XPath expression exists in the document.
  • It returns 1, representing True, if the XQuery expression in a query returns a nonempty result. That is, it returns at least one XML node.
  • It returns 0, representing False, if it returns an empty result.
  • The Syntax for this is: exist (XQuery)
  • This will become more clear to you if you see the following query:
SELECT S_ID FROM StudXML
WHERE StudData.exist('/Student/Subject[@Sub_ID = "20C"]')= 1

The result can be seen as:

3_SQL_Server_XML_Data_Type_Methods_Part2

Query ():

  • The query () method takes an XQuery expression and evaluates it to a list of XML elements that can be accessed and processed further.
  •  The result is of xml type. The method returns an instance of untyped XML.
  • The Syntax can be seen as:

query (‘XQuery’)

  • This will become more clear, if you see the following example:
DECLARE @Doc xml
SET @Doc = '<Root>
<StudentDetails S_ID="1" StudName="Piyush Bajaj">
<Subjects>
  <Maths>2 books for reference</Maths>
  <Physics>3 books for reference</Physics>
</Subjects>
</StudentDetails>
</Root>'
SELECT @Doc.query('/Root/StudentDetails/Subjects') AS Link_to_Subjects

The result can be seen as:

4_SQL_Server_XML_Data_Type_Methods_Part2

Modify ():

  • The modify () method is used to perform XML DML operations on an XML document.
  • It allows inserting, updating, or deleting XML elements or attributes within an XML document.
  • The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.
  • The Syntax can be seen as:

modify (XML_DML)

  • An error is returned if the modify() method is called on a null value or results in a null value.

Well, this was all about XML Data Type Methods.

Hope you got it understood well 🙂

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published. Required fields are marked *