CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery
Tibor Nagy
Tibor Nagy

Reputation: 3

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

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!

Upvotes: 0

Views: 232

Answers (3)

lptr
lptr

Reputation: 6788

..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;

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

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

Upvotes: 2

marc_s
marc_s

Reputation: 754250

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)

Upvotes: 1

Related Questions