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:
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.