Menu
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>
649
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.
119
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.
30
Here is what i tried and it worked for me:
Here is the file
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
