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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-09-29 : 10:37:33
Thanks!
Go to Top of Page
   

- Advertisement -