This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 5.
You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference.
Book On Amazon | Book On Flipkart
OPENXML has been around longer than the XML data type in SQL Server. The OPENXML requires you to use a series of system stored procedures and a variable to keep track of a handle. Using the number handle was a formality as the real work was in the patterns getting the right values from the right nodes. In this post we will shred XML with an XPath into nodes without needing to use OPENXML or keep track of numbered handles. This is possible with the nodes() method of the XML data type.
All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 5” in the resource folder in a file named Lab6.3StarterSetup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”
The nodes() Method
Take a look at the following code. It has three song records in XML stored in an XML data type variable called @Doc. If we run the code and SELECT it we will see the link appear in the result set. Note: If you don’t want to re-type this then open the Lab6.3Starter.sql from the resource folder or SQL 2012 series Volume 5 from Joes2Pros.com.
DECLARE @Doc XML
</pre> <pre>SET @Doc='<Music> <Song TitleID="13159"> <<span class="GRnoSuggestion GRcorrect">WriterName</span>>Neil Diamond</WriterName> <Title>Red-Red Wine</Title> <Singer OrderID="1"> <<span class="GRcorrect">BandName</span>>Neil Diamond</BandName> </Singer> <Singer OrderID="2"> <<span class="GRcorrect">BandName</span>>UB40</BandName> </Singer> </Song> <Song TitleID="13160"> <<span class="GRnoSuggestion GRcorrect">WriterName</span>>Prince</WriterName> <Title>Manic Monday</Title> <Singer OrderID="1"> <<span class="GRcorrect">BandName</span>>The Bangles</BandName> </Singer> </Song> <Song TitleID="13161"> <WriterName>Roy Orbison</WriterName> <Title>Pretty Woman</Title> <Singer OrderID="1" BandName="Roy Orbison"> <BandName>Roy Orbison</BandName> </Singer> <Singer OrderID="2"> <BandName>Van Halen</BandName> </Singer> </Song> </Music>'</pre> <pre>
SELECT @Doc
Right now it looks like the three nodes are in one record in our result set. If we open the link and collapse the top-level nodes notice we have three songs or three nodes at the /Music/Song level.
How do we turn this into three records of XML fragments as opposed to one complete well-formed XML? The three /Music/Song level nodes will represent our three records. The nodes() method will produce the result needed. Instead of selecting the @Doc directly lets SELECT FROM @DOC with the nodes() method. The nodes() method will need to know the XPath. We want to see all three songs. Since each song is at the /Music/Song level we will use the following XPath:
SELECT
FROM @Doc.nodes('/Music/Song')
The FROM clause normally expects a named table to produce a tabular result. Because the nodes() method in this example is in the FROM clause it needs to represent and look like a table. To do this we will simply alias the method. In this example we will alias this AS a table named Songs.
SELECT
FROM @Doc.nodes('/Music/Song') AS Songs
Sometimes tables have no records but they always have fields. Like any table it has at least one field. Most tables have many fields. So a table is likely to contain a collection of records but sometimes a table may have no records at all. A table must have at least one field. That means a table is guaranteed to be a collection of at least one field. In other words a table will have a collection of columns even if it has no records.
Currently our Songs table does not have any defined way to refer to the collection of fields that make up this table alias. If we look back at the original XML there are several field options from the different elements and attributes such as TitleID, WriterName, BandName, etc. We need to specify to our Songs table that all these possible fields are inside a collection which we will call SongRow:
SELECT
FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)
SongRow is like a suitcase that holds all of our field names for the table Songs. The first value we want to pull out is going to be the Title. From the Songs table, SELECT the SongRow collection and query the (‘Title’) element:
SELECT SongRow.query('Title')
FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)
To recap the nodes() method, alias the tablename with the rows stored within the column collection (or field collection) in parentheses. In the SELECT statement specify the column(s).
Combining value() and nodes() methods
The nodes() method can shred an XML data type into a tabular stream while allowing us to identify the rowpattern that make up each node. Let’s start this section where we left off.
By specifying that our node is at the /Music/Song level we returned three XML fragments. The first record is <Title>Red-Red Wine</Title> which is a very small XML fragment showing us that song title in an XML element. What if we wanted Red-Red Wine as VARCHAR data without XML? In other words we don’t need the data with tags showing as XML fragments. We really want parsed values not XML fragments to make up our three rows. This is a simple change in the query. Change the SELECT list to use the value() method instead of query() and add a second argument that specifies the data type. The value() method needs a singleton [1] so add that to the Title in the first parameter as seen in the following code:
SELECT SongRow.value('Title[1]', 'VARCHAR(100)')
FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)
By using the value() method with the nodes() method we were able to pull VARCHAR data from the XML stream. We want to pull out another field from the SongRow column collection. The <Title> element worked and is a child of <Song>. The <TitleID> is also a child of <Song> as an attribute. The nice thing about an attribute is they are guaranteed to be singletons.
It makes sense for ID to be the first field listed in a query so we can drop the Title down to the second line of our SELECT list and create another field. Let’s make TitleID our first field by placing it in front of Title in the SELECT list. TitleID is an INT and we will alias it as TitleID and Title AS Title:
SELECT SongRow.value('@TitleID', 'INT') AS TitleID,
SongRow.value('Title[1]','VARCHAR(100)') AS Title
FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)
Since attributes are guaranteed to be singletons it is not necessary to specify [1] in the query. This code now pulls out two fields from the SongRow collection since we used two different value() methods (one for each field).
We were able to pull out the TitleID as well as the Title using the value() method and nodes() method together. Let’s take a moment and look at the XML we are working from.
Let’s use what we know and pull out the first BandName for each singer. BandName is not a direct child of the /Music/Song node, it is a grandchild. Since the specified node level is /Music/Song how do we reach down two more levels for BandName? In the value() method we can specify the additional levels needed. In this case it is Singer[1]/BandName[1]. These are elements so we need to specify the singleton to pull the first Singer and first BandName of each song:
SELECT SongRow.value('@TitleID', 'INT') AS TitleID,
SongRow.value('Title[1]','VARCHAR(100)') AS Title,
SongRow.value('Singer[1]/BandName[1]', 'VARCHAR(100)')
AS BandName
FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)
Using the value() method along with the nodes() method we have been able to shred our XML without using OPENXML or handles.
Reference: Pinal Dave (https://blog.sqlauthority.com)
