asked on

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?

Thanks!!
T20090210.xml.txt

Raja Jegan R

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

ASKER CERTIFIED SOLUTION

PedroCGD

membership

This solution is only available to members.

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

Start Free Trial

sillydevilmc

ASKER

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