Geek Answers Handbook

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

I have XML (see below).

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

I am stuck in the description of the xsd file. In Visual Studio, when I click the Create XSD button, I get the message "Cannot output XSD from an XML file. XML contains multiple namespaces."

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

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

thank

<?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>
+5
source share
4 answers

SSIS XML. , ACMEHdr, Msg, Pmt, TransResp .. , <Namespace:Element>. , , , .

SSIS XML, . XML SSIS . OperationType XSLT, SourceType , .

SaveOperationResult True OperationResult. DestinationType File Connection Destination XML .

xslt.

<?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>

, XML : http://www.drdobbs.com/windows/219700581

XML, SecondOperandType XSLT .

XML, , XSLT . XML . XML. , , , .

XML XML . " XSD", .

"" XML, , , . , (, ). , (255- Unicode) .

, XML- SSIS XML. XML, XSD XSLT.

+9

XML XML , ? , XML , - , , , , . , sp_xml_preparedocument OPENXML .

+2

, :

Here is the file

+2
source

SSIS namespace support has been added since 2012 (in the XML job).

0
source

More articles:


All Articles