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

x

Need help importing XML to SQLServer by SSIS

Medium Priority
753 Views
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?

Thanks!!
T20090210.xml.txt
Comment
Watch Question

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

Commented:
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.
<ROOT>
<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"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>

Open in new window

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.
Helped?
Regards,

Pedro
www.pedrocgd.blogspot.com

SSIS-Interface0.JPG
SSIS-Interface.JPG
StyleSheet.xsl.txt
Package-EE86-dtsx.txt
XMLOutput-xml.txt
XMLOutput-xsd.txt

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

Ask the Experts

Author

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
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.

OR

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.