question

Derrick Smith avatar image
Derrick Smith asked

SSIS - Convert XML Column to table

Hi All, Possibly a complex question here but I'm hoping not. In SSIS, I know how to take source XML from a variable or query and convert it to a table. This works fine. My question is this: How can I make this work when my XML source is a column from a query? I.e. when I have a query that returns 4 columns, and 1 is an XML column with all the data I need? I would like to Multi-cast this and convert the XML to a recordset/variable that I can merge back into later down in the package. Is it possible to convert XML from one column into its own recordset? Or do I need to do something like set up a loop, insert the XML into a variable, create an XML source, and use that variable? That seems like a junky (and slow) way of doing this, so I'm hoping someone else has a better solution. Thanks in advance :D -Derrick
ssisxml
10 |1200 characters needed characters left characters exceeded

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

Dave Morrison avatar image
Dave Morrison answered
Hi Derrick, I may be missing the point but can you not do the extracting / xml shredding in the sql query before it gets into SSIS?
10 |1200 characters needed characters left characters exceeded

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

Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
You could do something as simple as use a Data Flow task, select the source to be the XML, and the destination a table in a SQL Database, you can then choose to use as many or as few columns as you like and the data is somewhere that is readily available for you to use again.
10 |1200 characters needed characters left characters exceeded

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.