Package with XMLDatasource Migrated to SSIS 2014 with Error: “the data source you are using does not support mixed content in elements..”


When I migrate an SSIS package which includes an XMLDatasource to import data from SSIS 2008 to SSIS 2014,
I ran into the following error:
“the data source you are using does not support mixed content in elements..”
We have no control for the coming XML file so I decide to move the logic into database
and use an Execute SQL Task to pull data from the coming XML file.

Here is the query I used for the SQL Task:


--Target table
CREATE TABLE [dbo].[SomeTable](
	[cat-code] [nvarchar](255) NULL,
	[class-code] [nvarchar](255) NULL,
	[subclass-code] [nvarchar](255) NULL,
	[ad-number] [nvarchar](255) NULL,
	[start-date] [nvarchar](255) NULL,
	[end-date] [nvarchar](255) NULL
	
) 


--Solution query

declare @tmp table (XmlCol XML)

INSERT INTO @tmp(XmlCol) 
SELECT * FROM OPENROWSET( BULK 'E:\X\data\myXMLfeed.xml', SINGLE_BLOB) AS x ;
 


 insert into  [dbo].[Sometable]

SELECT 
   p.value('(./cat-code)[1]', 'NVARCHAR(255)') AS [cat-code],
   p.value('(./class-code)[1]', 'NVARCHAR(255)') AS [class-code],
   p.value('(./subclass-code)[1]', 'NVARCHAR(255)') AS [subclass-code],
   p.value('(./ad-number)[1]', 'NVARCHAR(255)') AS [ad-number],
   p.value('(./start-date)[1]', 'NVARCHAR(255)') AS [start-date],
   p.value('(./end-date)[1]', 'NVARCHAR(255)') AS [end-date]     
FROM @tmp 
   CROSS APPLY XmlCol.nodes('/web-export/run-date/pub-code/ad-type/.') t(p)

 
Advertisements


Leave a Reply

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