Need help importing XML to SQLServer by SSIS

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

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    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.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now