ansaurus

Question

How do you take an XML file and insert its values into a database table using SSIS (SQL Server 2005)

Answer 1

+2 A:

Have you tried loading the raw XML into a XML data type in a database table and then converting the data into database tables via stored procedures? I generally prefer this method so that I can preserve the original XML code in case something changes in the schema that I am unaware of at the time of the import such as the source system adding new nodes to the file. If you go with this approach, then you could use sp_xml_preparedocument and OPENXML to convert the data.

Registered User 2010-09-05 15:05:34

This is a good answer, but I need to do this task from within SSIS.

codingguy3000 2010-09-07 14:42:34

Answer 2

+2 A:

Here is what i tried and it worked for me:

I saved your xml as .xml file.
I opened it in Visual Studio 2008.
On the Xml Editor toolbar i clicked "create schema" icon.
This generated 5 xsd files.
I ran xsd.exe file.xsd file2.xsd fileN.xsd /classes
This generated a C# class that represents the schemas.
From here you can use SSIS script source component to de-serialize your xml file into dataflows and use other SSIS tasks to do the inserts into SQL.

Here is the file

unclepaul84 2010-09-07 19:02:31

Answer 3

+2 A:

SSIS does not handle multiple namespaces in the XML source file. Your file has namespaces that include ACMEHdr, Msg, Pmt, TransResp, etc. You can find examples where you see the format <Namespace:Element>. The first step to avoid multiple namespaces is to transform your source file to a format that doesn’t refer to the namespaces.

SSIS has an XML task that can do the transformation. Add the XML task to an SSIS Control Flow and edit it. Change the OperationType property value to XSLT, the SourceType to File connection and the Source to your source file that has the problem.

Set the SaveOperationResult property to True and expand the OperationResult branch. Set DestinationType to File Connection and the Destination to a new XML file.

Add the following to a new file and save it with an xslt file extension.

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0"         xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
  <xsl:output method="xml" indent="no" />
  <xsl:template match="/|comment()|processing-instruction()">
    <xsl:copy>
      <xsl:apply-templates />
    </xsl:copy>
  </xsl:template>
  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="@*|node()" />
    </xsl:element>
  </xsl:template>
  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}">
      <xsl:value-of select="." />
    </xsl:attribute>
  </xsl:template>
</xsl:stylesheet>

This code comes from the following article, which also gives more information about cleaning up a source XML file: http://www.drdobbs.com/windows/219700581

Back in the XML task, set the SecondOperandType to File connection and the Second Operand to your new XSLT file.

When you run the XML task, it will take your original file and apply the transformation rules defined in the XSLT file. The results will be saved in your new XML file. This task only needs to be run once for the original XML file. When you look at the new file, you’ll see the same data as in the original, but without namespace references.

Now you can return to your data flow and alter the XML Source to reference the new XML file. Click on the Generate XSD and you should be able to avoid your error.

When you click on the Columns tab in your XML Source, you will probably see a warning. This is because the data types may not be fully defined (for example there’s no mention of string lengths). This shouldn’t be a problem as long as the default data type (255-character Unicode string) meets your needs.

Here are two references that you can use to work with the SSIS XML Task and XML Source objects. And here are good resources for XML, XSD and XSLT.

bobs 2010-09-08 00:54:31

Thanks for the answer. When you say "XML Task" what are you referring to? I see an XML Source, but under Data Flow Transformations I don't see anything related to XML. What are see are Pointer, Aggregate, Audit, Character Map, Conditional Split, etc.

codingguy3000 2010-09-08 15:26:54

The Data Flow Transformations are tools that can be used in the Data Flow task. These tools only appear in your tool box when you are viewing the details of a Data Flow. An XML Task is not part of a Data Flow, so you need to switch to the Control Flow tab. When viewing this tab, you will see Control Flow Items in your toolbox. This is where you found the Data Flow Task and will find the XML Task.

bobs 2010-09-08 16:45:55

You win the bounty. Can I ask one more question. I'm getting an error "Cannot convert between unicode and non-unicode string data type." Any idea where this error message is comming from? Is it how I have the table defined?

codingguy3000 2010-09-10 17:53:03

Thanks for the bounty. The error is a common when you have WSTR to STR data type. This is only allowed when you explicitly cast from one to the other. If you know the data flow component that gets the error, you can view the input and output properties for the columns. For some components, such as MERGE JOIN, you can edit the component and place the mouse pointer over each column and view the tooltip. If you don't see the information in the edit, then right-click the component and select Show Advanced Editor. There will be an Input and Output Properties tab. The data types are in there.

bobs 2010-09-10 18:10:29

I forgot to mention, when you find the case where there are two different data types, you need to determine how to fix that problem. If you really need to convert from WSTR to STR, then you need a explicitly convert the current column to a new column (use Derived Column or Data Conversion component).

bobs 2010-09-10 18:13:44

@bobs Thanks for everything. This task was very difficult for me and I appreciate your help. Cheers!

codingguy3000 2010-09-10 18:26:30

@bobs I just posed a question that you might be able to help with. http://stackoverflow.com/questions/3772514/ssis-execute-a-stored-procedure-with-the-parameters-from-csv-file-sql-server-200

codingguy3000 2010-09-22 18:39:07

ansaurus

tags:

views:

answers:

How do you take an XML file and insert its values into a database table using SSIS (SQL Server 2005)

related questions