在 T-SQL 中分析嵌套 XML 需要在单行中具有父项和子项

Question

正如标题所说。我有这样的XML：

<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>

我有这样的 SQL：

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)

它返回 2 行，但我需要将第二个事件作为 3 个单独的行返回，以便我可以看到包含 4 行和所有不同记录 ID 的集合

如何使用 T-SQL 读取这样的 XML？

Answer 1

查询嵌套节点的正确方法是由APPLY调用的级联.nodes()：

您的示例 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>';

-

-查询

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);

结果

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

简而言之，这个想法：

我们使用.nodes()将每个<Event>作为单独的行获取。
现在我们再次使用 .nodes()，但我们使用第一次调用返回的 XML 片段执行此操作，并使用相对路径(开头没有斜杠(。
第二个.nodes()将每个<Event>中的每个<Record>作为单独的行返回。

如果有兴趣：这个答案显示了为什么我们永远不应该使用向后导航(在XPath中使用../(。

Answer 2

从最里面的元素开始，使用..在层次结构中向上：

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)