We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Need help importing XML to SQLServer by SSIS

Medium Priority
Last Modified: 2013-11-10
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?

Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

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

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.



Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


May i know how you generate the xsd file? How does the XSLT work to convert it to readable XML. Thanks for your help
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.