Systems Engineering and RDBMS

Different XML Data Type Methods

Posted by decipherinfosys on May 29, 2009

We have covered the different XML data type methods before in our blog posts.  Over here, we are just aggregating all of them together and will show the differences between each of those methods.  The xml data type allows 5 different methods to allow us to query, modify or shred the XML data and present it to us in a record format.  The 5 methods are:

a) nodes() method:  This method can be used to convert XML data into row/column format.  Here is the link for all the posts that we have done on the nodes method and those posts also have links to the BOL and other related articles.

b) exist() method: This method helps us to specify XQuery on the XML instance and if the value matches, it returns back 1 and if non-matches, it gives back a 0.  We had covered it in our post here.  Take a look at the word doc. attachment in that post and it shows how to declare a variable and use it in the exist() method.

c) value() method: This method allows us to perform XQuery on a xml instance and return a scalar value back.  One of the posts in which we had covered this was the one where we had compared two different XML variables – here.

d) query() method: This method also allows us to perform XQuery on a xml instance and return back an untyped XML to us.   We had covered this in our post on the ReportServer DB Queries – here.  And the last method in the list is:

e) modify() method: This method is new in SQL Server 2008.   As the name also suggests, this method is used do DML operations against a XML instance.  We had covered this here and here.

We have seen that not many DBA’s spend much time in learning about XML and the XML capabilities of SQL Server.  We would highly recommend getting familiar with all the enhancements in SQL Server 2005 and SQL Server 2008.  Key is to also know when to use it and how to use it.  One of the best books out there on this topic is the book by Michael Coles – Pro SQL Server 2008 XML.

3 Responses to “Different XML Data Type Methods”

  1. Want different Xml conditions like comparison between 2 variables.

  2. want a code for comparision between two variables.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: