How to extract repeating data from XML in SQL Server-CodePudding

How do I return all the barcode exceptions (with the barcode range IDs)? The SELECT statement below only returns the first instance of a barcode exception.

I cannot change the data format or the way I get it (i.e. in a temp table) -- only the SELECT statement.

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


-- Actual outcome: only first instance is returned
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);

-- Desired outcome
/*
BarcodeRangeId       Exception
-------------------- -------------
1001                 12345678911
1001                 12345678912
1003                 12345678905
*/

CodePudding user response：

What you need to do is use nodes to the nodes you actually want here so that you get one row per value of that node. It seems, here, that you should first go to the node Product/Barcodes/BarCodeRanges/BarcodeRange and then from that node also go to Exceptions/Barcode. Then you can get the values you need:

SELECT BCR.BR.value('(BarcodeRangeId/InternalId/text())[1]', 'varchar(20)') AS BarcodeRangeId,
       E.B.value('(./text())[1]', 'varchar(13)') AS Exception
FROM #Products P --"x" isn't for "Products"
     CROSS APPLY P.xmlData.nodes('Product/Barcodes/BarCodeRanges/BarcodeRange') BCR(BR)
     CROSS APPLY BCR.BR.nodes('Exceptions/Barcode') E(B);