Loading XML Documents into SQL Server 2005 - Part 4

This is the fourth in a series of posts.  You can find the previous posts here (1, 2, 3).

In my previous posts, the data in an xml file was retrieved as a single row.  It is more realistic to consider an xml file that contains multiple rows of data.  The file doc3.xml contains three instances of the sample document from previous posts surrounded with a <docs> tag. 

LoadFile3.sql contains an updated version of the query which works with this new file.  The from clause uses the nodes() method to extract each document instance into a seperate row.  Each row contains an XML variable which can be accessed using the same XML Data Type methods from the previous examples.

There are two interesting things to note:

The first is that the context node for the XML variable is set to /doc.  "." is used to represent the context node in the XPath statements. 

The second is that the XML variable can not be returned directly.  So, to select back its XML contents, the query method needs to be used.  

For more info, see the following books online topics:

nodes() Method (XML Data Type)

-David