x

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
more ▼

asked Apr 30, 2012 at 08:50 PM in Default

Derrick Smith gravatar image

Derrick Smith
84 3 3 4

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

2 answers: sort voted first

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?

more ▼

answered May 01, 2012 at 02:42 PM

Dave Morrison gravatar image

Dave Morrison
472 2 3 4

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered May 01, 2012 at 02:43 PM

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

(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: Apr 30, 2012 at 08:50 PM

Seen: 1260 times

Last Updated: May 01, 2012 at 02:43 PM