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
asked Apr 30, 2012 at 08:50 PM in Default
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.
answered May 01, 2012 at 02:43 PM
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?
answered May 01, 2012 at 02:42 PM