Hello, I have a table which has a column of type XML. My ETL logic requires me to run XPATH expressions on each row of data and then upload the results into DB. I looked at the XMLTask in the Control flow, but it only applies to one document and not to row-by-row. In the DataFlow there is no XMLTask which allows to apply XPath expressions on each row of data. Now should I resort to the "script" task?? or is there any elegant way to handle the XML column.

You could run through the data row by row and pass each XML column into a variable, then use the XML source to read that variable.Not pretty, though, and not sure it helps with your XPath expressions. I suspect you are right and the script component will be your friend here...JamesJames Beresford @ http://www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia

Could you make use of For each loop task.before the foreach loop get ur data from SQL table using ur query and store the value in an object variable.Next use the For each loop with ADO enumerator.Hope this helps !! Sudeep| My Blog

What this essentially means is that SSIS has no knowledge of the xml data type in SQL Server. This is a shame because xml datatypes were introduced quite some time ago!

Hello, I have a table which has a column of type XML. My ETL logic requires me to run XPATH expressions on each row of data and then upload the results into DB. I looked at the XMLTask in the Control flow, but it only applies to one document and not to row-by-row. In the DataFlow there is no XMLTask which allows to apply XPath expressions on each row of data. Now should I resort to the "script" task?? or is there any elegant way to handle the XML column. A recent script has been released , which demonstrates how you can implement extraction of information using XPath in the data flow.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/