Need help importing XML to SQLServer by SSIS

Hello all,

I'm having some problems importing some XML data files into our SQL Server 2005 database.  I've attached a sample xml data file that was given to me by the client, but SSIS doesn't like it.  I have several data files to process, this is just one of them, but they all seem to have the same problem.  

When I create a data flow tax with an XML source, I browse to the xml data file, and tried to use the "Generate XSD" option in the XML source adapter and got a different message: "Unable to infer the XSD from the XML file.  The XML contains multiple namespaces."

I've done a LOT of research online and can't seem to find a solution that works.  DO I need to ask the client to provide better data or is this something that can be fixed?  Is there some typeof pre-processing I can do to fixup the XML source files so SSIS can read them?

Who is Participating?
PedroCGDConnect With a Mentor Commented:
Dear Friend,
I made an example for you. Pay attention to understand it weel.
1. Create a new SSIS Project
2. Add the attached SSIS package
3. Update all the connections in the package to the files attached.
run the package.

The package use a XML task in control flow, to convert the original XML file in a readable XML file.


Raja Jegan RSQL Server DBA & ArchitectCommented:
The XML File which you have provided was having multiple namespaces and is not able to be read by SQL Server.

Please find below a sample XML with a single namespace so that It can read it easily.

If possible convert your XML to a valid format and try it once again.
If your XML is converted to below syntax, then you can easily load it.
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>

Open in new window

sillydevilmcAuthor Commented:
May i know how you generate the xsd file? How does the XSLT work to convert it to readable XML. Thanks for your help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.