SQL Server 2019使用XQuery解析XML关联表头明细避免重复值

核心逻辑 #

不要拆分Header、站点节点、明细节点后再关联查询，直接从最细粒度的Value读数明细节点出发，通过XPath轴向上查询所属父级站点、根级Header的字段，从根源避免笛卡尔积。

代码实现 #

预设XML结构（可根据实际业务结构调整XPath路径） #

<DeliverMeterReading>
  <Header>
    <BatchId>XXX</BatchId>
    <CreateTime>2024-01-01</CreateTime>
  </Header>
  <DeliverySite>
    <DeliverySiteEANCode>123456789</DeliverySiteEANCode>
    <Meter>
      <MeterNo>MT001</MeterNo>
      <Values>
        <Value Time="2024-01-01 00:00" Reading="100.5" />
        <Value Time="2024-01-01 01:00" Reading="101.2" />
      </Values>
    </Meter>
  </DeliverySite>
</DeliverMeterReading>

对应查询语句 #

SELECT
  -- 取根节点Header字段
  T.ReadingXML.value('(/DeliverMeterReading/Header/BatchId)[1]', 'varchar(50)') AS BatchId,
  T.ReadingXML.value('(/DeliverMeterReading/Header/CreateTime)[1]', 'datetime') AS CreateTime,
  -- 向上查找当前Value节点所属的站点编码
  v.Node.value('ancestor::DeliverySite[1]/DeliverySiteEANCode[1]', 'varchar(20)') AS DeliverySiteEANCode,
  -- 向上查找当前Value节点所属的电表编号
  v.Node.value('ancestor::Meter[1]/MeterNo[1]', 'varchar(20)') AS MeterNo,
  -- 取当前Value节点的明细读数属性
  v.Node.value('@Time', 'datetime') AS ReadingTime,
  v.Node.value('@Reading', 'decimal(18,2)') AS ReadingValue
FROM 你的业务表 T
-- 直接定位所有最细粒度的Value节点，一行对应一条读数明细
CROSS APPLY T.ReadingXML.nodes('/DeliverMeterReading//Value') v(Node)

关键说明 #

• ancestor::是XPath轴语法，代表查找当前节点的所有上级节点，加[1]取最近的符合条件的父级节点，性能优于多层嵌套APPLY写法
• 无需多层CROSS APPLY拆分不同层级节点再做关联，天然避免多节点拆分后的笛卡尔积问题
• 若存在无读数明细的站点/电表节点，将CROSS APPLY替换为OUTER APPLY即可保留对应上级节点数据

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