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

avatar 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

avatar image

Oleg
17.2k 3 7 28

(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.

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:

x1153
x189

asked: Jun 01, 2011 at 06:35 AM

Seen: 1759 times

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

Copyright 2016 Redgate Software. Privacy Policy