SQL Server XML Data Type Methods – Part 1

Hello Folks,

You would have surely heard about XML which is been getting a significant increase in the popularity and usage of XML in the past few years.

In-fact almost all the modern applications seem to be touching XML in one way or the other. They would either generate XML for consumption by other applications or components, or they would either consume the XML produced by the others.

For e.g., .NET applications use XML files to store configuration information. ASP.NET web pages are XML documents, and almost all modern websites generate and publish information as XML feeds.

The XML data type supports a number of methods that allow various operations on the XML document. The most common operations needed on an XML document might be reading values from elements or attributes, querying for specific information, or modifying the document by inserting, updating, or deleting XML elements or attributes.

The several methods for the XML Data Type methods are as follows:

  • XPath
  • value()
  • nodes()
  • exist()
  • query()
  • modify()

We will be going on dealing with each of them, one by one:

XPath:

  • XPath is used for locating XML elements and attributes within an XML document and locating through the XML tree.
  • Every element and attribute within an XML document has a unique “path”.
  • For e.g.,

<Students>

<S_Id>01</S_Id>

<S_Id>02</S_Id>

</Students>

 

  • So, the path for the first element is “/Students/S_Id[1]” and for the second is “/Students/S_Id[2]”.
  • Each element and attribute within an XML document can be uniquely identified and processed using an XPath expression.

Value ():

  • It is being used to retrieve scalar values from an XML document as a relational column.
  • It takes an XQuery expression and evaluates it to a single node, casts results to the specified SQL Server data type, and so it returns the value.
  • For e.g. , this is a query to find out the S_Id and Grade of a Student.
DECLARE @a XML
SELECT @a = '<Student S_Id = "1" Grade = "A1" />'
 
SELECT
@a.value('(Student/@S_Id)[1]' , 'INT') AS S_Id,
@a.value('(Student/@Grade)[1]' , 'CHAR(2)') AS Grade

The result can be seen as:

1_SQL_Server_XML_Data_Type_Methods_Part1

  • The Value () method accepts an XPath expression pointing to the element or attribute to read data from.
  • It also specifies the data type of the result column.

Well, this was enough for this article-post. In the next article-post, will like to deal with the rest!!

So be tuned!!

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

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 *