Solved

XML to Table (MS SQL)

Posted on 2014-10-03
7
260 Views
Last Modified: 2015-08-04
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
Comment
Question by:Petter A. Halseth
  • 4
  • 3
7 Comments
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
ID: 40359297
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
 

Author Comment

by:Petter A. Halseth
ID: 40359330
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
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40359352
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Petter A. Halseth
ID: 40359375
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
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40360159
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
 

Author Comment

by:Petter A. Halseth
ID: 40363345
Hi,
Great thanx! It solved my problems!

P.
0
 

Author Comment

by:Petter A. Halseth
ID: 40914857
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question