asked on

to extract data from xml file to sql table using ssis

I have a xml file I have been trying to import data from to sql server using ssis packages. After some research I learnt that there are namespaces that I have to get rid of so, I put an xml task to clean the xml file. I have given both the files below.

After that I generate an xsd file which gives an error:
"Pipeline component has returned HRESULT error code 0xC02092A1 from a method call
Error at data flow Task [XML Source[1]]: The XML Source Adapter does not support mixed content model on Complex Types."

Thank You for the help.

Megan Brooks

You probably have mixed content within your XML - child nodes mixed with text.

I haven't made much use myself of the XML task or data source. It works if your data meets its restrictions. The task API allows for specifying namespace information -- although I have never tried to use that feature -- but apparently the GUI does not.

There is a lot you can do with XML in SQL Server once you have loaded an XML document into a row in a table that contains an XML data type column. I have not found mixed content to be a problem as long as the XQuery against the column is written to match what's there.

Rahul Nandipati

ASKER

The Actual data doesn't start until row index 16, before that it is all headers and data that is not needed.
Could you tell me a way that I can import only that data?

Thank You for your help.

ASKER CERTIFIED SOLUTION

Megan Brooks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rahul Nandipati

ASKER

Well, I just changed the input file from xml to an excel sheet.

I did the same thing that you said, so I am just going to mark this as the best answer.

Thank you for your help.