question

Xploring_SSIS avatar image
Xploring_SSIS asked

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!
ssisxml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
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 = ' A1B1C1 A2B2C2 A3B3C3 A4B4C4 '; -- 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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.