Extract XML data from SQL table

Solution proposed by Susheel :




  txt xml


INSERT INTO @tmp (txt)

select AlertXML FROM [AP].[dbo].[AlertInfo]

SELECT txt.value(‘(//SPREADINFO/SPREAD_CODE)[1]’, ‘nvarchar(50)’) SPREAD_CODE,txt.value(‘(//SPREADINFO/SPREAD_DESC)[1]’, ‘nvarchar(50)’) SPREAD_DESC

FROM   @tmp


Select * from @tmp


This will give the result in a table format and then can passed to ssis pipeline.


Other Solution,

Put a foreach loop and navigate row by row. fill the xml data to an object type package variable, then a DFT –> xml data source can pick data from the variable and pass to the destination.


Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s