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

xml sql-server-2005 ssis xsd

12,673

Solution 1

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

Solution 2

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.

Solution 3

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

12,673

Author by

codingguy3000

I am a SQL Server developer

Updated on June 08, 2022

Comments

codingguy3000 almost 2 years

I have XML (see below).

I need to insert the records in this XML file into a SQL Server 2005 table using SSIS.

I'm stuck with the xsd file description. Within Visual Studio when I click the Generate XSD button I get the message "Unable to infer the XSD from the XML file. The XML contains multiple namespaces."

Does anyone know of a good resource or tutorial that can help me soft this problem?

Please note that I need to perform this task in SSIS.

Thanks

<?xml version="1.0" encoding="utf-8" ?> 
<Envelope>
    <Body>
        <Env>
            <ACMEHdr:ACMEResponseHdr xmlns:ACMEHdr="ACMEResponseHdr">
                <ACMEHdr:ProcessorName>ACME PREPAID SOLUTIONS</ACMEHdr:ProcessorName>
                <ACMEHdr:FeedName>ACMEPMTRSP</ACMEHdr:FeedName>
                <ACMEHdr:FileDate>08122010</ACMEHdr:FileDate>
                <ACMEHdr:WorkOfDate>08112010</ACMEHdr:WorkOfDate>
                <ACMEHdr:FileSeqNumber>000024</ACMEHdr:FileSeqNumber>
            </ACMEHdr:ACMEResponseHdr>
            <Msg:Message xmlns:Msg="Message">
                <Pmt:PaymentResponse xmlns:Pmt="PaymentResponse">
                    <Pmt:TransactionResponse xmlns:TransResp="TransactionResponse">
                        <TransResp:trnDate>0711201002:10:01.123456</TransResp:trnDate>
                        <TransResp:intTransactionRefId>131BE5E1-701A-42FA-AF8C-D2D38FDCC2EA</TransResp:intTransactionRefId>
                        <TransResp:transAmt>11.88</TransResp:transAmt>
                        <TransResp:strDebitCreditCode>DR</TransResp:strDebitCreditCode>
                        <TransResp:transCurrencyCd>840</TransResp:transCurrencyCd>
                        <TransResp:userID></TransResp:userID>
                        <TransResp:transAmtUSD>11.88</TransResp:transAmtUSD>
                        <TransResp:orderNbr>AM219003F700</TransResp:orderNbr>
                        <TransResp:ACHTransactionID></TransResp:ACHTransactionID>
                        <TransResp:fundSourceType>02</TransResp:fundSourceType>
                        <TransResp:sessionId>MondaySep12201022134314153720392</TransResp:sessionId>
                        <TransResp:requestToken>ACHMEN414d51204c50505741373838202020202421824b12246620</TransResp:requestToken>
                        <TransResp:authorizationCode>A98765</TransResp:authorizationCode>
                        <TransResp:reasonCode></TransResp:reasonCode>
                        <TransResp:reasonCodeDesc></TransResp:reasonCodeDesc>
                        <TransResp:addressMatch>Y</TransResp:addressMatch>
                        <TransResp:postalCdMatchInd>Y</TransResp:postalCdMatchInd>
                        <TransResp:SENbr>295176482889</TransResp:SENbr>
                        <TransResp:SEName>American Express PASS Card Fee</TransResp:SEName>
                        <TransResp:transResponse>A</TransResp:transResponse>
                    </Pmt:TransactionResponse>
                    <Pmt:TransactionResponse xmlns:TransResp="TransactionResponse">
                        <TransResp:trnDate>0711201002:15:01.123456</TransResp:trnDate>
                        <TransResp:intTransactionRefId>46233C40-3C33-4914-B447-B3E60BB04148</TransResp:intTransactionRefId>
                        <TransResp:transAmt>11.88</TransResp:transAmt>
                        <TransResp:strDebitCreditCode>DR</TransResp:strDebitCreditCode>
                        <TransResp:transCurrencyCd>840</TransResp:transCurrencyCd>
                        <TransResp:userID></TransResp:userID>
                        <TransResp:transAmtUSD>11.88</TransResp:transAmtUSD>
                        <TransResp:orderNbr>AM009003F701</TransResp:orderNbr>
                        <TransResp:ACHTransactionID>ACH1234567</TransResp:ACHTransactionID>
                        <TransResp:fundSourceType>05</TransResp:fundSourceType>
                        <TransResp:sessionId>MondayAug12201022134214123456789</TransResp:sessionId>
                        <TransResp:requestToken></TransResp:requestToken>
                        <TransResp:authorizationCode></TransResp:authorizationCode>
                        <TransResp:reasonCode></TransResp:reasonCode>
                        <TransResp:reasonCodeDesc></TransResp:reasonCodeDesc>
                        <TransResp:addressMatch></TransResp:addressMatch>
                        <TransResp:postalCdMatchInd></TransResp:postalCdMatchInd>
                        <TransResp:SENbr></TransResp:SENbr>
                        <TransResp:SEName></TransResp:SEName>
                        <TransResp:transResponse>A</TransResp:transResponse>
                    </Pmt:TransactionResponse>
                </Pmt:PaymentResponse>
            </Msg:Message>
            <ACMEFtr:ACMEResponseFooter xmlns:ACMEFtr="ACMEResponseFooter">
                <ACMEFtr:countDetail>2</ACMEFtr:countDetail>
                <ACMEFtr:countCreditDetail>0</ACMEFtr:countCreditDetail>
                <ACMEFtr:countDebitDetail>2</ACMEFtr:countDebitDetail>
                <ACMEFtr:hashTotalAmt>23.76</ACMEFtr:hashTotalAmt>
                <ACMEFtr:hashTotalCreditAmt>0.00</ACMEFtr:hashTotalCreditAmt>
                <ACMEFtr:hashTotalDebitAmt>23.76</ACMEFtr:hashTotalDebitAmt>
            </ACMEFtr:ACMEResponseFooter>
        </Env>
    </Body>
</Envelope>

codingguy3000 over 13 years

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

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.
bobs over 13 years

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.
codingguy3000 over 13 years

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?
bobs over 13 years

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 over 13 years

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).
codingguy3000 over 13 years

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

@bobs I just posed a question that you might be able to help with. stackoverflow.com/questions/3772514/…
HBCondo over 10 years

This doesn't appear to be the case. In a SSIS project created with the BI 2012 suite, you still get the error message "The XML contains multiple namespaces". See this blog post for details: bidn.com/blogs/RussLoski/ssas/2670/…