More on reading XML in SQL Server
Posted by decipherinfosys on November 3, 2008
We have covered reading and writing XML data in SQL Server in several of our blog posts – one such example is this post. One of the recent questions that was posed was about de-constructing the data stored in an XML data-type column. In all our examples before we had shown that using a variable and hence the question – what the end user wanted to do was to de-construct not just one record but all of them and wanted to do so in a single SQL statement. It can be done easily by using the same technique as we had shown for a single variable.
Let’s pick up an example and walk through it since that is the best way to learn and understand how to go about doing this work. You can look at the two data records over here.
If you see, the data record has information about the company and it’s contacts. There is information in that data record that has other pieces of information about the company and the rest of the information related to company – contacts etc. is part of the XML. And that is what we need to de-construct.
We can once again use the nodes method and the CROSS APPLY as well as the OUTER APPLY operators in order to render that information. You can access the whole code for de-constructing the data over here. One key thing to note is that we used the OUTER APPLY when the data may or may not be present in the nodes since that relationship was not mandatory while we used CROSS APPLY when we knew that the data has to be present in the child. You can read more about the APPLY operator over here.
There are other ways of getting to this information as well but having the nodes() method provides for a very simple and quick (and performant) way of getting all the information that we need.