x

XML data not read in SSIS

Hi,

I am trying to create a SSIS package to read data from oracle database and xml file and load it into XML file. The package was built without any error but when I executed the package the data is not read right from XML source transformation and is returning NULL for all the columns.

Am I missing something? Please help!
more ▼

asked Jun 01, 2011 at 06:35 AM in Default

Xploring_SSIS gravatar image

Xploring_SSIS
31 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If NULL for all columns is returned then it probably means that the transformation is correct as far as the xml shape is concerned, but the casing of the node names does not match. For example:

declare @xml xml;

-- let it have 3 columns 4 rows shape
set @xml = '<rows>
    <row><Col1>A1</Col1><Col2>B1</Col2><Col3>C1</Col3></row>
    <row><Col1>A2</Col1><Col2>B2</Col2><Col3>C2</Col3></row>
    <row><Col1>A3</Col1><Col2>B3</Col2><Col3>C3</Col3></row>
    <row><Col1>A4</Col1><Col2>B4</Col2><Col3>C4</Col3></row>
</rows>';

-- select with shape correct, but casing wrong
-- correctly returns the desired number of columns
-- and rows, but all the values are NULL
select
    item.value('col1[1]', 'varchar(5)') Col1,
    item.value('col2[1]', 'varchar(5)') Col2,
    item.value('col3[1]', 'varchar(5)') Col3
    from @xml.nodes('rows/row') R(item);

 results:
Col1  Col2  Col3
----- ----- -----
NULL  NULL  NULL
NULL  NULL  NULL
NULL  NULL  NULL
NULL  NULL  NULL

-- lets fix the casing and see the results:
select
    item.value('Col1[1]', 'varchar(5)') Col1,
    item.value('Col2[1]', 'varchar(5)') Col2,
    item.value('Col3[1]', 'varchar(5)') Col3
    from @xml.nodes('rows/row') R(item);

-- correct results:
Col1  Col2  Col3
----- ----- -----
A1    B1    C1
A2    B2    C2
A3    B3    C3
A4    B4    C4

Please check the spelling of the nodes which actually hold desired values to make sure that each one is spelled correctly and the case matches the source.

Oleg
more ▼

answered Jun 01, 2011 at 08:24 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x927
x146

asked: Jun 01, 2011 at 06:35 AM

Seen: 1149 times

Last Updated: Jun 01, 2011 at 06:35 AM