Parsing XML Complex Types via SQL

I worked on a couple interesting stored procedures at a client this week that dealt with parsing & building XML data. Complex types turned out to be a little more challenging than I realized. Or, to put it another way, I realized my SQL skills when it comes to parsing XML were inadequate when dealing with a complex type.

After trudging through MSDN for an hour or so I finally figured out what I needed and felt I should write a cliff notes version for future reference.

Take a look at the example message I built below as I’ll use this throughout this article.

<Events>
    <Event>
        <EventType>Update</EventType>
        <EventName>Customer.Address</EventName>
    </Event>
    <Event>
        <EventType>Update</EventType>
        <EventName>Customer.Phone</EventName>       
    </Event>
    <Event>
        <EventType>Update</EventType>
        <EventName>Customer.Email</EventName>       
    </Event>
</Events>

Parsing Complex Types from an XML object

Parsing an XML object containing a complex type can be done via the Nodes method.

SELECT @xml = CAST('<Events><Event><EventType>Update</EventType><EventName>Customer.Address</EventName></Event><Event><EventType>Update</EventType><EventName>Customer.Phone</EventName></Event><Event><EventType>Update</EventType><EventName>Customer.Email</EventName></Event></Events>' AS XML);

INSERT INTO @Events (EventName, EventType)
SELECT 
    tbl.col.value('EventType[1]','VARCHAR(100)'),
    tbl.col.value('EventName[1]','VARCHAR(100)')
FROM @xml.nodes('/Events/Event') AS tbl(col)

If you look at the documentation the nodes method creates a relational table of the data returned from the XQuery you provided as a parameter. You can then turn around and query the returned table result with regular SQL. For this example, I will insert into a temporary table.

Creating XML objects with a Complex Type

If you need to create an XML object containing a complex type, you can do this via a nested SELECT utilizing FOR XML. This is a handy little tool for returning your SQL query results as XML which can then be used for creating your XML object.

DECLARE @MsgXml XML

SELECT @MsgXml = 
                    (SELECT
                        (SELECT EventType       AS 'EventType',
                            EventName       AS 'EventName'
                        FROM @Events
                        FOR XML PATH('Event'), TYPE)
                    FOR XML PATH('Events'), TYPE)

This uses the temporary table from the previous example to recreate the original message. One thing to point out is the use of the PATH mode. Which essentially lets you declare the parent element of your select query. FOR XML also is using the TYPE parameter, which lets SQL Server know it needs to return an XML type object.

That’s all there is to it.