Import XML only gives Null results

Question

Post reply

Import XML only gives Null results

Martin 45512

SSC Veteran

Points: 250
More actions
May 13, 2015 at 4:39 am

#74516

SOLVED my self
Using this file located in path mentioned in Openrowset
<sqlexec>
<viewfmt>
<cbaado>097</cbaado>
<cbaadv>superscriptie</cbaadv>
<cbamdt>20020121</cbamdt>
<cbaag9>VERWIJJ</cbaag9>
</viewfmt>
<viewfmt>
<cbaado>098</cbaado>;
<cbaadv>zorgverklaring</cbaadv>
<cbamdt>20010914</cbamdt>
<cbaag9>VERWIJJ</cbaag9>
</viewfmt>
</sqlexec>
and this script:
if OBJECT_ID('Table1','U') is not null
drop table Table1
GO
CREATE TABLE dbo.Table1 (
Field1varchar(100),
Field2varchar(100),
Field3varchar(10),
Field4varchar(100));
GO
DECLARE @table TABLE (XMLData XML);
INSERT INTO @table
SELECT *
FROM OPENROWSET(BULK N'C:\XML\Table1.xml', SINGLE_BLOB) rs;
INSERT INTO dbo.Table1 (Field1, Field2, Field3, Field4)
SELECT CBAADO = x.data.value('CBAADO[1]','varchar(3)'),
CBAADV = x.data.value('CBAADV[1]','varchar(55)'),
CBAMDT = x.data.value('CBAMDT[1]','varchar(10)'),
CBAAG9 = x.data.value('CBAAG9 [1]','varchar(10)')
FROM @table t
CROSS APPLY t.XMLData.nodes('/sqlexec/viewfmt') x(data);
with result
(1 row(s) affected)
(2 row(s) affected)
Select result:
Field1 Field2 Field3 Field4
---------------------------------
NULL NULL NULL NULL
NULL NULL NULL NULL
(2 row(s) affected)
Why those null results :/
In other cases it works fine
Import into Excel gives no problem, but I don't want to use that option
Who can help me out? (Files attached)
Saved the probleem case sensitive ...
you may close it

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

That's correct, XML is case sensitive.

It's worth noting that you can improve you performance by adding a reference to the text node like so:

SELECT CBAADO = x.data.value('(cbaado/text())[1]','varchar(3)'),

CBAADV = x.data.value('(cbaadv/text())[1]','varchar(55)'),

CBAMDT = x.data.value('(cbamdt/text())[1]','varchar(10)')

FROM @table t

CROSS APPLY t.XMLData.nodes('/sqlexec/viewfmt') x(data);

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

I was looking at the title of this post and thinking "Hmmmm, some XML that's actually space efficient". 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Jeff Moden (5/17/2015)
I was looking at the title of this post and thinking "Hmmmm, some XML that's actually space efficient". 😛

The ultimate compression!

😎

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Martin 45512 (5/13/2015)
DECLARE @table TABLE (XMLData XML);
INSERT INTO @table
SELECT *
FROM OPENROWSET(BULK N'C:\XML\Table1.xml', SINGLE_BLOB) rs;

Quick thought, no need to use the table variable

😎

;WITH SOURCE_DATA AS

(

SELECT

CONVERT(XML,RS.BulkColumn,1) AS SD_XML

FROM OPENROWSET(BULK N'C:\EE_DATA\Downloads\Example XML\Table1.xml', SINGLE_BLOB) RS

)

SELECT

VIEWFMT.DATA.value('cbaado[1]','varchar(3)')

,VIEWFMT.DATA.value('cbaadv[1]','varchar(55)')

,VIEWFMT.DATA.value('cbamdt[1]','varchar(10)')

,VIEWFMT.DATA.value('cbaag9[1]','varchar(10)')

FROM SOURCE_DATA SD

OUTER APPLY SD.SD_XML.nodes('/sqlexec/viewfmt') VIEWFMT(DATA)

Martin 45512 SSC Veteran Points: 250 More actions · Answer 5

Thanks for the improvements.

I do normally use a lot of variables in my scripts, but in this case it might be better to do not for @table because the actual load will be massive.

test i'm doing is only max 500 rows per table. The declare in this case can give some extra workload.