SQL Server中如何用单查询拆分多层节点XML？求助解决方案

这个多层级XML的拆分确实有点绕，但用SQL Server自带的XML数据类型方法完全可以通过单个查询搞定。我结合你给出的XML结构，写个具体的示例一步步拆解：

首先，假设你的XML内容是存储在变量里的（如果是表列的话，只需要替换成表列即可）：

DECLARE @xml XML = '<Report>
  <P>
    <Data>
      <Cust custID = "A" custName = "B" ></Cust>
    </Data>
  </P>
  <H>
    <Data1>
      <Seats>
        <Seat id = "abc" value = "123" ></Seat>
        <Date depart = "abc1" arrive = "1231" ></Date>
      </Seats>
      <Records>
        <Record recID = "C" col2 = "D" ></Record>
        <Record recID = "E" col2 = "F" ></Record>
      </Records>
    </Data1>
  </H>
</Report>';

SELECT
  -- 提取P节点下的客户属性
  Cust.value('@custID', 'VARCHAR(50)') AS CustID,
  Cust.value('@custName', 'VARCHAR(50)') AS CustName,
  -- 提取Seats节点下的座位信息
  Seat.value('@id', 'VARCHAR(50)') AS SeatID,
  Seat.value('@value', 'VARCHAR(50)') AS SeatValue,
  -- 提取Seats节点下的日期信息
  DepartDate.value('@depart', 'VARCHAR(50)') AS DepartDate,
  DepartDate.value('@arrive', 'VARCHAR(50)') AS ArriveDate,
  -- 提取Records节点下的记录信息
  Record.value('@recID', 'VARCHAR(50)') AS RecID,
  Record.value('@col2', 'VARCHAR(50)') AS Col2
FROM
  @xml.nodes('/Report') AS Report(Rpt)
-- 关联P层级的客户节点
CROSS APPLY
  Rpt.nodes('P/Data/Cust') AS PData(Cust)
-- 关联H层级的座位节点
CROSS APPLY
  Rpt.nodes('H/Data1/Seats/Seat') AS Seats(Seat)
-- 关联H层级的日期节点
CROSS APPLY
  Rpt.nodes('H/Data1/Seats/Date') AS Dates(DepartDate)
-- 关联H层级的记录节点
CROSS APPLY
  Rpt.nodes('H/Data1/Records/Record') AS Records(Record);

关键逻辑说明： #

• .nodes()方法用来定位XML中的目标节点，返回一个包含节点行的结果集；
• CROSS APPLY用来逐层关联子节点，相当于把每个层级的节点进行笛卡尔积组合，确保所有层级的信息都能关联输出；
• .value()方法负责提取节点的属性值，第一个参数是属性的XPath路径（@属性名表示取节点属性），第二个参数是转换后的SQL数据类型。

适配表列场景 #

如果你的XML是存储在表的列中（比如表名为ReportTable，XML列名为ReportXML），只需要把变量替换成表列即可：

SELECT
  Cust.value('@custID', 'VARCHAR(50)') AS CustID,
  Cust.value('@custName', 'VARCHAR(50)') AS CustName,
  Seat.value('@id', 'VARCHAR(50)') AS SeatID,
  Seat.value('@value', 'VARCHAR(50)') AS SeatValue,
  DepartDate.value('@depart', 'VARCHAR(50)') AS DepartDate,
  DepartDate.value('@arrive', 'VARCHAR(50)') AS ArriveDate,
  Record.value('@recID', 'VARCHAR(50)') AS RecID,
  Record.value('@col2', 'VARCHAR(50)') AS Col2
FROM
  ReportTable
CROSS APPLY
  ReportXML.nodes('/Report/P/Data/Cust') AS PData(Cust)
CROSS APPLY
  ReportXML.nodes('/Report/H/Data1/Seats/Seat') AS Seats(Seat)
CROSS APPLY
  ReportXML.nodes('/Report/H/Data1/Seats/Date') AS Dates(DepartDate)
CROSS APPLY
  ReportXML.nodes('/Report/H/Data1/Records/Record') AS Records(Record);

可选优化：处理非必填节点 #

如果某些子节点可能不存在（比如部分Report没有Records），可以把CROSS APPLY换成OUTER APPLY，这样即使子节点缺失，上层的信息依然会保留，缺失字段会显示为NULL。

内容的提问来源于stack exchange，提问作者DC07