title: How do you take an XML file and insert its values into a database table using SSIS (SQL Server 2005)
body: <p>I have XML (see below). </p>
  
  <p>I need to insert the records in this XML file into a SQL Server 2005 table using SSIS. </p>
  
  <p>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."</p>
  
  <p>Does anyone know of a good resource or tutorial that can help me soft this problem? </p>
  
  <p>Please note that I need to perform this task in SSIS. </p>
  
  <p>Thanks</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt; 
  &lt;Envelope&gt;
      &lt;Body&gt;
          &lt;Env&gt;
              &lt;ACMEHdr:ACMEResponseHdr xmlns:ACMEHdr="ACMEResponseHdr"&gt;
                  &lt;ACMEHdr:ProcessorName&gt;ACME PREPAID SOLUTIONS&lt;/ACMEHdr:ProcessorName&gt;
                  &lt;ACMEHdr:FeedName&gt;ACMEPMTRSP&lt;/ACMEHdr:FeedName&gt;
                  &lt;ACMEHdr:FileDate&gt;08122010&lt;/ACMEHdr:FileDate&gt;
                  &lt;ACMEHdr:WorkOfDate&gt;08112010&lt;/ACMEHdr:WorkOfDate&gt;
                  &lt;ACMEHdr:FileSeqNumber&gt;000024&lt;/ACMEHdr:FileSeqNumber&gt;
              &lt;/ACMEHdr:ACMEResponseHdr&gt;
              &lt;Msg:Message xmlns:Msg="Message"&gt;
                  &lt;Pmt:PaymentResponse xmlns:Pmt="PaymentResponse"&gt;
                      &lt;Pmt:TransactionResponse xmlns:TransResp="TransactionResponse"&gt;
                          &lt;TransResp:trnDate&gt;0711201002:10:01.123456&lt;/TransResp:trnDate&gt;
                          &lt;TransResp:intTransactionRefId&gt;131BE5E1-701A-42FA-AF8C-D2D38FDCC2EA&lt;/TransResp:intTransactionRefId&gt;
                          &lt;TransResp:transAmt&gt;11.88&lt;/TransResp:transAmt&gt;
                          &lt;TransResp:strDebitCreditCode&gt;DR&lt;/TransResp:strDebitCreditCode&gt;
                          &lt;TransResp:transCurrencyCd&gt;840&lt;/TransResp:transCurrencyCd&gt;
                          &lt;TransResp:userID&gt;&lt;/TransResp:userID&gt;
                          &lt;TransResp:transAmtUSD&gt;11.88&lt;/TransResp:transAmtUSD&gt;
                          &lt;TransResp:orderNbr&gt;AM219003F700&lt;/TransResp:orderNbr&gt;
                          &lt;TransResp:ACHTransactionID&gt;&lt;/TransResp:ACHTransactionID&gt;
                          &lt;TransResp:fundSourceType&gt;02&lt;/TransResp:fundSourceType&gt;
                          &lt;TransResp:sessionId&gt;MondaySep12201022134314153720392&lt;/TransResp:sessionId&gt;
                          &lt;TransResp:requestToken&gt;ACHMEN414d51204c50505741373838202020202421824b12246620&lt;/TransResp:requestToken&gt;
                          &lt;TransResp:authorizationCode&gt;A98765&lt;/TransResp:authorizationCode&gt;
                          &lt;TransResp:reasonCode&gt;&lt;/TransResp:reasonCode&gt;
                          &lt;TransResp:reasonCodeDesc&gt;&lt;/TransResp:reasonCodeDesc&gt;
                          &lt;TransResp:addressMatch&gt;Y&lt;/TransResp:addressMatch&gt;
                          &lt;TransResp:postalCdMatchInd&gt;Y&lt;/TransResp:postalCdMatchInd&gt;
                          &lt;TransResp:SENbr&gt;295176482889&lt;/TransResp:SENbr&gt;
                          &lt;TransResp:SEName&gt;American Express PASS Card Fee&lt;/TransResp:SEName&gt;
                          &lt;TransResp:transResponse&gt;A&lt;/TransResp:transResponse&gt;
                      &lt;/Pmt:TransactionResponse&gt;
                      &lt;Pmt:TransactionResponse xmlns:TransResp="TransactionResponse"&gt;
                          &lt;TransResp:trnDate&gt;0711201002:15:01.123456&lt;/TransResp:trnDate&gt;
                          &lt;TransResp:intTransactionRefId&gt;46233C40-3C33-4914-B447-B3E60BB04148&lt;/TransResp:intTransactionRefId&gt;
                          &lt;TransResp:transAmt&gt;11.88&lt;/TransResp:transAmt&gt;
                          &lt;TransResp:strDebitCreditCode&gt;DR&lt;/TransResp:strDebitCreditCode&gt;
                          &lt;TransResp:transCurrencyCd&gt;840&lt;/TransResp:transCurrencyCd&gt;
                          &lt;TransResp:userID&gt;&lt;/TransResp:userID&gt;
                          &lt;TransResp:transAmtUSD&gt;11.88&lt;/TransResp:transAmtUSD&gt;
                          &lt;TransResp:orderNbr&gt;AM009003F701&lt;/TransResp:orderNbr&gt;
                          &lt;TransResp:ACHTransactionID&gt;ACH1234567&lt;/TransResp:ACHTransactionID&gt;
                          &lt;TransResp:fundSourceType&gt;05&lt;/TransResp:fundSourceType&gt;
                          &lt;TransResp:sessionId&gt;MondayAug12201022134214123456789&lt;/TransResp:sessionId&gt;
                          &lt;TransResp:requestToken&gt;&lt;/TransResp:requestToken&gt;
                          &lt;TransResp:authorizationCode&gt;&lt;/TransResp:authorizationCode&gt;
                          &lt;TransResp:reasonCode&gt;&lt;/TransResp:reasonCode&gt;
                          &lt;TransResp:reasonCodeDesc&gt;&lt;/TransResp:reasonCodeDesc&gt;
                          &lt;TransResp:addressMatch&gt;&lt;/TransResp:addressMatch&gt;
                          &lt;TransResp:postalCdMatchInd&gt;&lt;/TransResp:postalCdMatchInd&gt;
                          &lt;TransResp:SENbr&gt;&lt;/TransResp:SENbr&gt;
                          &lt;TransResp:SEName&gt;&lt;/TransResp:SEName&gt;
                          &lt;TransResp:transResponse&gt;A&lt;/TransResp:transResponse&gt;
                      &lt;/Pmt:TransactionResponse&gt;
                  &lt;/Pmt:PaymentResponse&gt;
              &lt;/Msg:Message&gt;
              &lt;ACMEFtr:ACMEResponseFooter xmlns:ACMEFtr="ACMEResponseFooter"&gt;
                  &lt;ACMEFtr:countDetail&gt;2&lt;/ACMEFtr:countDetail&gt;
                  &lt;ACMEFtr:countCreditDetail&gt;0&lt;/ACMEFtr:countCreditDetail&gt;
                  &lt;ACMEFtr:countDebitDetail&gt;2&lt;/ACMEFtr:countDebitDetail&gt;
                  &lt;ACMEFtr:hashTotalAmt&gt;23.76&lt;/ACMEFtr:hashTotalAmt&gt;
                  &lt;ACMEFtr:hashTotalCreditAmt&gt;0.00&lt;/ACMEFtr:hashTotalCreditAmt&gt;
                  &lt;ACMEFtr:hashTotalDebitAmt&gt;23.76&lt;/ACMEFtr:hashTotalDebitAmt&gt;
              &lt;/ACMEFtr:ACMEResponseFooter&gt;
          &lt;/Env&gt;
      &lt;/Body&gt;
  &lt;/Envelope&gt;
  </code></pre>