CODEWITHSUNDEEP
sql-serverxmlt-sqlxml-parsing
katit
katit

Reputation: 17915

Parse nested XML in T-SQL need parent and childs in single row

As title says.. I have XML like so:

<logs>
  <Event>
    <DriverId>51</DriverId>
    <EventID>b31ade0f-1053-4df4-a9dd-ffc76060f3c5</EventID>
    <Records>
      <Record>
        <RecordID>b31ade0f-1053-4df4-a9dd-ffc76060f3c5</RecordID>
      </Record>
    </Records>
  </Event>
  <Event>
    <DriverId>45</DriverId>
    <EventID>3b454377-74c7-4ea2-909e-3ea239b969b3</EventID>
    <Records>
      <Record>
        <RecordID>3b454377-74c7-4ea2-909e-3ea239b969b3</RecordID>
      </Record>
      <Record>
        <RecordID>3b454377-74c7-4ea2-909e-3ea239b969b4</RecordID>
      </Record>
      <Record>
        <RecordID>3b454377-74c7-4ea2-909e-3ea239b969b5</RecordID>
      </Record>
    </Records>
  </Event>
</logs>

And I have SQL like this:

SELECT
e.col.value('./DriverId[1]', 'NVarChar(25)') DriverId,
e.col.value('./EventID[1]', 'UniqueIdentifier') EventId,
-- /Records/Record data:
e.col.value('./Records[1]/Record[1]/RecordID[1]', 'UniqueIdentifier') RecordID
FROM @XML.nodes('//Event') e(col)

It returns 2 rows, but I need second event to be returned as 3 separate rows so I can see set with 4 rows with all different Record IDs

How do I read XML like this with T-SQL?

Upvotes: 1

Views: 273

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

The correct way to query nested nodes is a cascade of .nodes() called by APPLY:

Your sample XML:

DECLARE @XML XML=
N'<logs>
  <Event>
    <DriverId>51</DriverId>
    <EventID>b31ade0f-1053-4df4-a9dd-ffc76060f3c5</EventID>
    <Records>
      <Record>
        <RecordID>b31ade0f-1053-4df4-a9dd-ffc76060f3c5</RecordID>
      </Record>
    </Records>
  </Event>
  <Event>
    <DriverId>45</DriverId>
    <EventID>3b454377-74c7-4ea2-909e-3ea239b969b3</EventID>
    <Records>
      <Record>
        <RecordID>3b454377-74c7-4ea2-909e-3ea239b969b3</RecordID>
      </Record>
      <Record>
        <RecordID>3b454377-74c7-4ea2-909e-3ea239b969b4</RecordID>
      </Record>
      <Record>
        <RecordID>3b454377-74c7-4ea2-909e-3ea239b969b5</RecordID>
      </Record>
    </Records>
  </Event>
</logs>';

--The query

SELECT A.evnt.value('(DriverId/text())[1]','int') AS Event_DriverId
      ,A.evnt.value('(EventID/text())[1]','uniqueidentifier') AS Event_EventId 
      ,B.rec.value('(RecordID/text())[1]','uniqueidentifier') AS Record_RecordId 
FROM @XML.nodes('/logs/Event') A(evnt)
OUTER APPLY A.evnt.nodes('Records/Record') B(rec);

The result

Event_DriverId  Event_EventId                           Record_RecordId
51              B31ADE0F-1053-4DF4-A9DD-FFC76060F3C5    B31ADE0F-1053-4DF4-A9DD-FFC76060F3C5
45              3B454377-74C7-4EA2-909E-3EA239B969B3    3B454377-74C7-4EA2-909E-3EA239B969B3
45              3B454377-74C7-4EA2-909E-3EA239B969B3    3B454377-74C7-4EA2-909E-3EA239B969B4
45              3B454377-74C7-4EA2-909E-3EA239B969B3    3B454377-74C7-4EA2-909E-3EA239B969B5

The idea in short:

We use .nodes() to get each <Event> as a separate row.
Now we use .nodes() again, but we do this with the XML-fragment returned by the first call and we use a relative path (no slash at the beginning).
The second .nodes() returns each <Record> within each <Event> as a separate row.

Just if interested: This answer shows, why we should never use backward-navigation (using ../ in the XPath).

Upvotes: 3

Rubens Farias
Rubens Farias

Reputation: 57996

Start from your innermost element and use the .. to go up in the hierarchy:

SELECT
e.col.value('../../../DriverId[1]', 'NVarChar(25)') DriverId,
e.col.value('../../../EventID[1]', 'UniqueIdentifier') EventId,
e.col.value('.', 'UniqueIdentifier') RecordID
FROM @XML.nodes('//RecordID') e(col)

Upvotes: 0

Related Questions