如何在SQL Server中从XML提取含BarcodeRangeId的所有条码异常数据

在SQL Server环境下，需从临时表#Products的XML数据中返回所有条码异常及其对应的BarcodeRangeId。现有SELECT语句仅能返回每个条码范围的首个异常，要求不得修改数据格式与临时表创建方式，仅允许调整SELECT语句。

原测试代码 #

DROP TABLE IF EXISTS #Products
CREATE TABLE #Products (XmlData XML)

INSERT INTO #Products (XmlData)
VALUES 
(
'<Product>
  <Product>1</Product>
  <Name>Product 1</Name>
  <Barcodes>
    <BarCodeRanges>
      <BarcodeRange>
        <BarcodeRangeId>
          <InternalId>1001</InternalId>
        </BarcodeRangeId>
        <Name>Barcode Range 1</Name>
        <Start>12345678910</Start>
        <End>12345678919</End>
        <Exceptions>
          <Barcode>12345678911</Barcode>
          <Barcode>12345678912</Barcode>
        </Exceptions>
      </BarcodeRange>
      <BarcodeRange>
        <BarcodeRangeId>
          <InternalId>1002</InternalId>
        </BarcodeRangeId>
        <Name>Barcode Range 2</Name>
        <Start>12345678900</Start>
        <End>12345678910</End>
        <Exceptions>
          <Barcode>12345678905</Barcode>
        </Exceptions>
      </BarcodeRange>
    </BarCodeRanges>
  </Barcodes>
</Product>
<Product>
  <Product>2</Product>
  <Name>Product 2</Name>
</Product>'
)


-- 实际结果：仅返回首个异常实例
SELECT 
t.value('(Barcodes/BarCodeRanges/BarcodeRange/BarcodeRangeId/InternalId/text())[1]', 'varchar(20)') AS BarcodeRangeId,
t.value('(Barcodes/BarCodeRanges/BarcodeRange/Exceptions/Barcode/text())[1]', 'varchar(13)') AS Exception
FROM #Products x
CROSS APPLY x.xmlData.nodes('Product') a(t);

期望输出 #

BarcodeRangeId       Exception
-------------------- -------------
1001                 12345678911
1001                 12345678912
1002                 12345678905

原查询仅遍历到Product节点，无法展开每个BarcodeRange下的所有Barcode异常项。需通过多次CROSS APPLY逐层遍历XML节点：

1. 先遍历所有Product节点
2. 再遍历每个Product下的BarcodeRange节点
3. 最后遍历每个BarcodeRange下Exceptions中的Barcode节点

调整后的SELECT语句如下：

SELECT 
  br.value('(BarcodeRangeId/InternalId/text())[1]', 'varchar(20)') AS BarcodeRangeId,
  bc.value('(text())[1]', 'varchar(13)') AS Exception
FROM #Products x
CROSS APPLY x.xmlData.nodes('Product') AS a(p)
CROSS APPLY p.nodes('Barcodes/BarCodeRanges/BarcodeRange') AS b(br)
CROSS APPLY br.nodes('Exceptions/Barcode') AS c(bc);

结果说明 #

该语句会逐个展开每个层级的XML节点，确保每个条码异常都能关联到对应的BarcodeRangeId，最终输出与期望结果一致。

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