Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

XML to Table (MS SQL)

Hi,
I have a xml file looking like this:
<?xml version="1.0" encoding="UTF-8" ?><database>
  <DELTACONO>
    <version>1.0</version>
    <data information="Items">
      <items>
        <item id="03-TP">
          <description>
            <short><![CDATA[DELTACO U/UTP Cat5e patchkabel 0,3m, grå]]></short>
          </description>
          <ean>7340004657330</ean>
        </item>
        <item id="05-6420">
          <description>
            <short><![CDATA[Direktronik Swing - Koblingskabel - RJ-45 (hann) - RJ-45 (hann) - 50 cm - UTP - CAT 5e - grå]]></short>
          </description>
        </item>

With closings of course.

I use this query:

DECLARE @xmlData AS XML

SET @xmlData = (
  SELECT * FROM OPENROWSET (
    BULK 'D:\engine\importfiler\levInfo\deltaco\item2.xml', SINGLE_CLOB
  ) AS xmlData
)

SELECT ref.value('./item[1]', 'varchar(50)') AS SKU,
         ref.value('./ean[1]', 'varchar(50)') AS Ean
FROM @xmlData.nodes('/database/DELTACONO/data') AS Data(ref)

But i'm unable to get the data I want.
I only get 1 empty row.

This is how I want it to look:

SKU         EAN
03-TP      7340004657330
05-6420  

I'm pretty new to XML so a little help is needed...
0
Petter A. Halseth
Asked:
Petter A. Halseth
  • 4
  • 3
1 Solution
 
Christopher GordonSenior Developer AnalystCommented:
Try this:

SELECT
      ref.value('@id[1]', 'varchar(50)') AS SKU,
        ref.value('./ean[1]', 'varchar(50)') AS Ean

FROM @xmlData.nodes('/database/DELTACONO/data/items/item') AS Data(ref)

Note the difference between pulling values from XML attribute and XML element value.
0
 
Petter A. HalsethAuthor Commented:
Ah.. nice. I got it to work thanx!
I see that I also need to pull the <short> node as well. How can i do this so I get this on the same line?

Like this:
SKU         EAN                     Short
03-TP      7340004657330   DELTACO U/UTP Cat5e patchkabel 0,3m, grå
05-6420                               Direktronik Swing - Koblingskabel - RJ-45 (hann) - RJ-45 (hann) - 50 cm - UTP - CAT 5e - grå
P.
0
 
Christopher GordonSenior Developer AnalystCommented:
SELECT
            ref.value('@id[1]', 'varchar(50)') AS SKU,
        ref.value('./ean[1]', 'varchar(50)') AS Ean,
            ref.value('.', 'nvarchar(50)') as short

FROM @xmlData.nodes('/database/DELTACONO/data/items/item') AS Data(ref)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Petter A. HalsethAuthor Commented:
This xml thing is really confusing... but I'm getting there step by step.

I have a similar xml file with attributes and element values that look like this:
<DELTACONO>
    <version>1.0</version>
    <data information="Items Stock Price">
      <items>
        <item id="03-TP">
          <stock quantity="3874" />
          <price>
            <net>4,47</net>
          </price>
        </item>

I'm trying to get the output to look like this:

SKU    StockQuantity   NetPrice
03-TP  3874                  4,47

using this query:
SELECT  ref.value('@id[1]', 'varchar(50)') AS SKU,
            ref.value('@quantity[1]', 'varchar(50)') AS Stock,
            ref.value('.', 'varchar(50)') AS Price
FROM @xmlData.nodes('/database/DELTACONO/data/items/item') AS Data(ref)

But then I'm only get the SKU and Price. Stock comes up as NULL

P.
0
 
Christopher GordonSenior Developer AnalystCommented:
Sorry for delay,  got caught up in stuff...

If you still need help on this, here is some more code that might help:

SELECT  
            ref.value('@id[1]', 'varchar(50)') AS SKU,
            
            ref.value('(stock/@quantity)[1]', 'int') as stock,

            ref.value('(price/net)[1]', 'varchar(100)') as price,

            ref.value('(description/short)[1]', 'varchar(1000)') as descr
                         
FROM @xmlData.nodes('/database/DELTACONO/data/items/item') AS Data(ref)
0
 
Petter A. HalsethAuthor Commented:
Hi,
Great thanx! It solved my problems!

P.
0
 
Petter A. HalsethAuthor Commented:
Hi, another problem popped up here. Received another xml that I need to import as well.
This time it looks like this:

<Body>
        <Line ID="10">
            <ItemID Type="Techdata">2869909</ItemID>
            <ItemID Type="Manufacturer">F7N060B2C00</ItemID>
            <ItemID Type="EAN">722868996157</ItemID>
            <ItemID Type="CommodityCode">4202918090</ItemID>
            <CountryOrigin>TW</CountryOrigin>
            <ItemText>Cinema Stripe Cover Blacktop iPad Air</ItemText>
            <ItemQty Type="delivered">5</ItemQty>
            <ItemWeight Type="Net" Unit="kg">1.6</ItemWeight>
            <ItemReference>
                <CustLine>1</CustLine>
            </ItemReference>
        </Line>

And I need to extract these data:
            <ItemID Type="Techdata">2869909</ItemID>
            <ItemID Type="Manufacturer">F7N060B2C00</ItemID>
            <ItemID Type="EAN">722868996157</ItemID>
            <ItemID Type="CommodityCode">4202918090</ItemID>
How can I get the data in these lines?

Any help is very much appreciated.

Petter.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now