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!

score 2 · Accepted Answer · answered Sep 11 '20 at 14:52

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 |
+------+-------+-----------+----------+-------+

Yitzhak, no time for the moment, you might check this triple approach with a benchmark. — Shnugo, Sep 12 '20 at 10:09
Thank you guys! This does the thing. I agree with the bad XML structure but it comes from a program I have no access to but I'll ask the creators so they might consider that to be a future upgrade. Cheers! — Tibor Nagy, Sep 12 '20 at 18:35

score 1 · Answer 2 · answered Sep 11 '20 at 12:52

Your current XML structure is rather flawed...

What you should have (and that would easily allow to know what bits of information belong together) is an element per product - something like:

<ProductList>    
    <Product>
        <ID>A-1235</ID>
        <Quantity>100</Quantity>
        <Price>300</Price>
    </Product>
    <Product>
        <ID>A-12356</ID>
        <Quantity>110</Quantity>
        <Price>310</Price>
    </Product>
</ProductList>

because with the current structure you have, there's no proper and reliable way to know which ProductId, Quantity and Price belong together .... you won't be able to reliably get this information as you have it right now .....

With this structure, your query would be:

SELECT 
    C.value('(ID)[1]', 'nvarchar(20)') AS ID,
    C.value('(Quantity)[1]', 'int') AS Quantity,
    C.value('(Price)[1]', 'decimal(16,2)') AS Price
FROM 
    @Xml.nodes('/ROOT/ProductList/Product') AS T(C)

Yes this is the structure I met a few times. I'll be sure to use this structure but this time the XML comes from a program I have no access to (I forgot to mention this). Thank you for the answer. — Tibor Nagy, Sep 12 '20 at 18:42

score 0 · Answer 3 · answered Sep 11 '20 at 20:43

..jff..

declare @x xml = N'
<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>';

select 
    [1],[2],[0], 
    cast([2] as int) as Quantity
from
(
select
    x.n.value('.', 'varchar(20)') as val,
    (row_number() over(order by x.n)-1) / 3 as grpid,
    row_number() over(order by x.n) % 3 as rowid
from @x.nodes('/ROOT/ProductList/*') as x(n)
) as src
pivot
(
max(val) for rowid in ([1],[2],[0])
) as pvt;

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

3 Answers3