Need help importing XML to SQLServer by SSIS

Posted on 2009-02-11
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?

Question by:sillydevilmc
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23609892
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

LVL 22

Accepted Solution

PedroCGD earned 2000 total points
ID: 23610150
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.



Author Comment

ID: 23611609
May i know how you generate the xsd file? How does the XSLT work to convert it to readable XML. Thanks for your help

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question