In this post I will demonstrate how to query XML data. This is one of those things that is much easier to learn by example so I provided five examples. I won't get into how to get the data in or out of SQL Server because that topic is outside the scope of this article. All you need to know is that it's easy if you know a little WMI scripting, powershell or .NET.
In the examples below I will read XML data from:
- An XML fragment from a MS Word Doc
- An XML fragment from an SSRS Report
- An event from the Windows Event Viewer
- An XML fragment from an SSIS package
- An XML fragment from a SQL trace
For these examples I will not include the entire XML document because the XML can be quite verbose. Instead I am using XML fragments (a portion of the entire XML document). This way you can get the general idea without having to copy/paste a ton of XML.
Setting up the sample data
Use this code:
The contents of #data will look like this:
Reading XML data
Note my comments in the code
... and the results:
Today I demonstrated how to use T-SQL to read from different XML data structures. Thanks for reading!
Last Updated: 5/21/2013 (Posted)