Systems Engineering and RDBMS

Archive for September 2nd, 2007

Using nodes() method to de-construct XML as relational data

Posted by decipherinfosys on September 2, 2007

In SQL Server 2005, in addition to the OPENXML() functionality, new methods have been introduced which make de-construction of an XML into relational data very easy. OPENXML() has it’s overheads when it comes to large data sets and moreover, one had to use the sp_xml_preparedocument and other system stored procedures for it. In SQL Server 2005, the nodes() method can be used to create a row for each instance of the node. One can then easily traverse through the hierarchy of the data set and can also search upon those result sets using the “sql:variable” syntax. Let us illustrate this with an example. We will make use of the new xml data-type in SQL Server 2005:

In this example, we are using an XML variable and are assigning a sample XML to it – the XML has information on the Shareholder’s internal ID value, their shares, how they voted for a given meeting. If you notice, we go to the deepest level when retrieving the values and then using the relative paths, we get the meeting notification value as well. We use the value method to retrieve the values from the XML. In addition, we use the exist method to place the filter on this result set to show us only those records that indicate that the shareholder voted “FOR” in their vote actions. We are including the document since the formatting messes up the XML code. You can open this up in MS word and cut-and-paste into SQL Server Management Studio.


The output of this execution is:

Mtng_Notification Share_Holder_ID Votable_Shares Vote_Action
——————– ——————– ————————————— ———–
444 987654321 100.00000 FOR
999 45454545454 657.00000 FOR
999 676767676767 654.00000 FOR

Using the new enhanced XML features in SQL Server 2005, makes handling of the XML data-sets very easy – regardless of whether it is an attribute centric or an element centric XML. In future blog posts, we will cover more about the other different XML enhancements in SQL Server 2005 and will also start looking into the upcoming features in SQL Server 2008 as they relate to the XML functionality.

Posted in SQL Server | 3 Comments »