Selecting data from XML

Question

I'm trying to insert a row based on data extracted from a chunk of XML. Some columns need to initialized to node values a couple of nodes "deep" in the XML structure.

I can't seem to the query right. Here's what I got:

declare @xmlRaw xml = '
    <LogEntry>
        <SummaryMessage>Something bad happened</SummaryMessage>
        <Exception>
            <Type>System.ApplicationException</Type>
            <Message>A test of the error handling</Message>
        </Exception>
    </LogEntry>'

select
    LogEntryColumn.value('SummaryMessage[1]', 'varchar(10)') as SummaryMessage, -- works fine
    LogEntryColumn.query('Exception[1]').value('Message[1]', 'varchar(10)') as ExMessage -- not working
from 
    @xmlRaw.nodes('LogEntry[1]') as LogEntryTable(LogEntryColumn)

This outputs:

SummaryMessage ExMessage
-------------- ----------
Something      NULL

I've tried a raft of variations for the "ExMessage" column query but no joy.

Note that I'm using "LogEntryColumn.query(...).value(...)" because I want to check how that form performs versus something like:

select
    LogEntryColumn.value('SummaryMessage[1]', 'varchar(10)') as SummaryMessage, -- works fine
    ExceptionEntryColumn.value('Message[1]', 'varchar(10)') as ExMessage -- not working
from 
    @xmlRaw.nodes('LogEntry[1]') as LogEntryTable(LogEntryColumn)
    outer apply @xmlData.nodes('LogEntry[1]/Exception') as ExceptionTable(ExceptionEntryColumn)

Basically I'm wondering if multiple "outer apply" from clauses is better/worse than multiple .query(...) invocations.

Answer 1

Here is what you need.

SQL

DECLARE @xmlRaw XML = 
N'<LogEntry>
    <SummaryMessage>Something bad happened</SummaryMessage>
    <Exception>
        <Type>System.ApplicationException</Type>
        <Message>A test of the error handling</Message>
    </Exception>
</LogEntry>';

SELECT c.value('(SummaryMessage/text())[1]', 'varchar(100)') AS SummaryMessage
    , c.value('(Exception/Message/text())[1]', 'varchar(100)') AS ExMessage 
FROM @xmlRaw.nodes('/LogEntry') AS t(c);

Output

+------------------------+------------------------------+
|     SummaryMessage     |          ExMessage           |
+------------------------+------------------------------+
| Something bad happened | A test of the error handling |
+------------------------+------------------------------+