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:

The result can be seen as:


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

The result can be seen as:


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:

The result can be seen as:


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:

The result can be seen as:


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!!



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