Extract XML data from SQL table


Solution proposed by Susheel :

 

DECLARE @tmp TABLE

(

  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.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s