XML import - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

XML import

Author

Topic

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-09-23 : 07:08:23

Hi all,

I want to import a XML file like:

<node>
   <code>555555</code>
   <definition>Definition</definition>
   <name>Name</name>
   <attribute>
      <code>6666</code>
      <name>Brown</name>
      <definition>Brown 6666</definition>
      <attributeValue>
         <code>1111111</code>
         <name>yyyyyyyy</name>
      </attributeValue>
   </attribute>
   <attribute>
      <code>7777</code>
      <name>Black</name>
      <definition>Black 7777</definition>
      <attributeValue>
         <code>2222222</code>
         <name>xxxxxxxx</name>
      </attributeValue>
   </attribute>
</node>

I'm using the following code:

declare @XMLDocument xml

select @XMLDocument = CONVERT(XML, replace(Bulkcolumn, '%', ''), 2) 
FROM OPENROWSET(BULK 'D:\xmlfile.xml', SINGLE_BLOB) AS Result

SELECT 
X.a.query('code').value('.', 'varchar(1000)') as code,
X.a.query('definition').value('.', 'varchar(1000)') as definition,
X.a.query('name').value('.', 'varchar(1000)') as name,
X.a.query('attribute/code').value('.', 'varchar(1000)') as code,
X.a.query('attribute/name').value('.', 'varchar(1000)') as name,
X.a.query('attribute/definition').value('.', 'varchar(1000)') as definition,
X.a.query('attribute/attributeValue/code').value('.', 'varchar(1000)') as code,
X.a.query('attribute/attributeValue/name').value('.', 'varchar(1000)') as name
FROM @XMLDocument.nodes('node') AS x(a)

The problem is the result, see:
code definition name code name definition code name
555555 Definition Name 66667777 BrownBlack Brown 6666Black 7777 11111112222222 yyyyyyyyxxxxxxxx

It concats values like the code-column (4th column). I expect two result records, I get one result. What can I do?

Thank you

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-09-23 : 07:42:00

[code]
SELECT
X.a.query('code').value('.', 'varchar(1000)') as code,
X.a.query('definition').value('.', 'varchar(1000)') as definition,
X.a.query('name').value('.', 'varchar(1000)') as name,
X.a.query('code').value('.', 'varchar(1000)') as code,
X.a.query('name').value('.', 'varchar(1000)') as name,
Y.b.query('definition').value('.', 'varchar(1000)') as definition,
Y.b.query('attributeValue/code').value('.', 'varchar(1000)') as code,
Y.b.query('attributeValue/name').value('.', 'varchar(1000)') as name

FROM @XMLDocument.nodes('node') AS X(a)
CROSS APPLY x.a.nodes('attribute') as y(b)
[/code]

You want to show the result like this?
[code]
code definition name code name definition code name
555555 Definition Name 555555 Name Brown 6666 1111111 yyyyyyyy
555555 Definition Name 555555 Name Black 7777 2222222 xxxxxxxx
[/code]

sabinWeb MCP

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-09-29 : 08:11:02

Thanks!

I've got an other XML file with a different structure and I tried with the cross apply but can't get it to work:


<invoice>
 <header>
  <inv_nr>4017985</inv_nr>
  <ord_nr>1400018040</ord_nr>
 </header>
 <line id="1">
  <art_code>artcode1</art_code>
  <art_desc1>description1</art_desc1>
 </line>
 <line id="2">
  <art_code>artcode1</art_code>
  <art_desc1>description2</art_desc1>
 </line>
</invoice>

Can you helpt me with this?

Thanks

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-09-29 : 10:04:43

[code]
declare @XMLDocument xml =
'<invoice>
<header>
<inv_nr>4017985</inv_nr>
<ord_nr>1400018040</ord_nr>
</header>
<line id="1">
<art_code>artcode2</art_code>
<art_desc1>description1</art_desc1>
</line>
<line id="2">
<art_code>artcode1</art_code>
<art_desc1>description2</art_desc1>
</line>
</invoice>'

SELECT
y.b.value('inv_nr[1]', 'varchar(1000)') as inv_nr
,y.b.value('ord_nr[1]', 'varchar(1000)') as ord_nr

,z.c.value('art_code[1]', 'varchar(1000)') as art_code
,z.c.value('art_desc1[1]', 'varchar(1000)') as descriptio

FROM @XMLDocument.nodes('invoice') AS X(a)
CROSS APPLY x.a.nodes('header') as y(b)
CROSS APPLY x.a.nodes('line') as z(c)
[/code]

sabinWeb MCP

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-09-29 : 10:37:33

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources