Converting XML to SQL - Multiple elements in the same node problem with repeating element names

Question

I got a really simple XML file that I want to convert to a table.

XML structure:

<ROOT>
  <ID>ID-20</ID> (ONLY 1 ID per file, this will be the first column)
  <ProductList>    
      <ProductID>A-1235</ProductID>
      <Quantity>100</Quantity>
      <Price>300</Price>
      <ProductID>A-12356</ProductID>
      <Quantity>110</Quantity>
      <Price>310</Price>
      <ProductID>A-123567</ProductID>
      <Quantity>120</Quantity>
      <Price>320</Price>
      ...
  </ProductList> 
</ROOT>

The second column would be ProductID, the 3rd Quantity, the 4th Price.

I could make each ProductID appear in separate rows with the first column but I can't make the respective Quantity and Price show next to the ProductID.

My code so far:

  SELECT T.C.value('../../../ID[1]', 'nvarchar(20)') AS ID,
           C.value('.', 'nvarchar(20)') AS ProductID,
           C2.value('(text())[1]', 'nvarchar(20)') AS Quantity--,COMMENTED PRICE OUT FOR NOW
           --C2.value('(../Price/text())[1]', 'nvarchar(20)') AS Price
  FROM @Xml.nodes('/ROOT/ProductList/ProductID') AS T(C)
             cross apply C.nodes('../Quantity') AS T2(C2)

The Cross Apply part causes every Quantity to appear next to every ProductID. I can't figure out the correct way to align these columns.

I found some similar questions here but I just couldn't figure this out for my case as the XML structure is a bit different.

Could someone please help me with this? I'd appreciate it very much :)

Problem SOLVED! Many thanks to all who contributed!

Answer 1

I completely agree with @marc_s, the XML structure is very fragile.

In any case, here is a solution for the current scenario.

@Shnugo recently came up with this approach here: How to extract value form XML?

All credit goes to him.

SQL

DECLARE @xml XML =
N'<ROOT>
    <ID>ID-20</ID>
    <ProductList>
        <ProductID>A-1235</ProductID>
        <Quantity>100</Quantity>
        <Price>300</Price>
        <ProductID>A-12356</ProductID>
        <Quantity>110</Quantity>
        <Price>310</Price>
        <ProductID>A-123567</ProductID>
        <Quantity>120</Quantity>
        <Price>320</Price>...</ProductList>
</ROOT>';

WITH tally(Nmbr) AS
(
    SELECT TOP(@xml.value('count(/ROOT/ProductList/ProductID)','INT'))
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM master..spt_values
)
SELECT tally.Nmbr
      ,@xml.value('(/ROOT/ID/text())[1]','NVARCHAR(20)') AS ID
      ,@xml.value('(/ROOT/ProductList/ProductID[sql:column("tally.Nmbr")]/text())[1]','NVARCHAR(200)') AS ProductID
      ,@xml.value('(/ROOT/ProductList/Quantity[sql:column("tally.Nmbr")]/text())[1]','INT') AS Quantity 
      ,@xml.value('(/ROOT/ProductList/Price[sql:column("tally.Nmbr")]/text())[1]','INT') AS Price 
FROM tally;

Output

+------+-------+-----------+----------+-------+
| Nmbr |  ID   | ProductID | Quantity | Price |
+------+-------+-----------+----------+-------+
|    1 | ID-20 | A-1235    |      100 |   300 |
|    2 | ID-20 | A-12356   |      110 |   310 |
|    3 | ID-20 | A-123567  |      120 |   320 |
+------+-------+-----------+----------+-------+