ranio Ответов: 1

Как обработать несколько файлов XML и обновление в SQL?

Я хочу разобрать несколько xmls и обновить статус с помощью sql. Я буду передавать Xml-строку с уровня переднего плана. Формат Xml упоминается ниже и передает идентификатор первичного ключа для обновления нескольких данных в xml.

Я могу анализировать один xml-файл.

Xml format passed from front end are as below:
<pre><FTS>
    <WSI_OUT_IPI>
        <orderingCustomerAccount />
        <orderingCustomerName />
        <orderingCustomerAddress />
        <beneficiaryAccount />
        <beneficiaryAccountName />
        <beneficiaryAccountAddress />
        <instructedAmount>0</instructedAmount>
        <remittanceInfo />
        <WSI_OUT_IPIID>837</WSI_OUT_IPIID>
        <WSI_OUT_IPI_INTER_ID>837</WSI_OUT_IPI_INTER_ID>
        <UID>0</UID>
        <beneficiaryInstitution />
        <purposeofPayment />
        <sendersRef />
        <benBankreferenceNo />
        <ftsReferenceNo />
        <middleWareReferenceNo />
        <currency />
        <messageType />
        <additionalField1 />
        <additionalField2 />
        <additionalField3 />
        <additionalField4 />
        <additionalField5 />
        <additionalField6 />
        <additionalField7 />
        <additionalField8 />
        <additionalField9 />
        <IsDirectEntry />
        <CreatedBy />
        <CreatedDate>2018-10-25T10:34:09.5296514+05:30</CreatedDate>
        <ModifiedBy />
        <ModifiedDate>2018-10-25T10:34:09.5296514+05:30</ModifiedDate>
        <DBErrorCode />
        <DBErrorDetails />
        <CreditNarration />
        <DebitNarration />
        <TranscactionCode />
        <CreditAccountNo />
        <DebitAccountNo />
        <ChannelID>0</ChannelID>
        <Amount>0</Amount>
        <ForceCredit>false</ForceCredit>
        <RetryCount>0</RetryCount>
        <DebitExchangeRate>0</DebitExchangeRate>
        <CreditExchangeRate>0</CreditExchangeRate>
        <AvailableBalance>0</AvailableBalance>
        <IsChargePost>false</IsChargePost>
        <ChargePostAmount>0</ChargePostAmount>
        <RSA_Encrypted_AES_key />
        <reqRefNo />
        <RSASignature />
        <serviceId>WSIFFTS0001</serviceId>
        <isReversePost>false</isReversePost>
        <userName />
        <password />
        <IPAddress />
        <orderingCustomerBBAN />
        <purposeofPayment_Value />
        <TransactionId />
        <SFTPId>0</SFTPId>
        <TotalAmount>0</TotalAmount>
        <IsAvailableBalanceCheck>false</IsAvailableBalanceCheck>
        <VerifyAllTransactionXml />
        <IsCorePostingEnabled>false</IsCorePostingEnabled>
    </WSI_OUT_IPI>
    <WSI_OUT_IPI>
        <orderingCustomerAccount />
        <orderingCustomerName />
        <orderingCustomerAddress />
        <beneficiaryAccount />
        <beneficiaryAccountName />
        <beneficiaryAccountAddress />
        <instructedAmount>0</instructedAmount>
        <remittanceInfo />
        <WSI_OUT_IPIID>836</WSI_OUT_IPIID>
        <WSI_OUT_IPI_INTER_ID>836</WSI_OUT_IPI_INTER_ID>
        <UID>0</UID>
        <beneficiaryInstitution />
        <purposeofPayment />
        <sendersRef />
        <benBankreferenceNo />
        <ftsReferenceNo />
        <middleWareReferenceNo />
        <currency />
        <messageType />
        <additionalField1 />
        <additionalField2 />
        <additionalField3 />
        <additionalField4 />
        <additionalField5 />
        <additionalField6 />
        <additionalField7 />
        <additionalField8 />
        <additionalField9 />
        <IsDirectEntry />
        <CreatedBy />
        <CreatedDate>2018-10-25T10:34:12.1936506+05:30</CreatedDate>
        <ModifiedBy />
        <ModifiedDate>2018-10-25T10:34:12.1936506+05:30</ModifiedDate>
        <DBErrorCode />
        <DBErrorDetails />
        <CreditNarration />
        <DebitNarration />
        <TranscactionCode />
        <CreditAccountNo />
        <DebitAccountNo />
        <ChannelID>0</ChannelID>
        <Amount>0</Amount>
        <ForceCredit>false</ForceCredit>
        <RetryCount>0</RetryCount>
        <DebitExchangeRate>0</DebitExchangeRate>
        <CreditExchangeRate>0</CreditExchangeRate>
        <AvailableBalance>0</AvailableBalance>
        <IsChargePost>false</IsChargePost>
        <ChargePostAmount>0</ChargePostAmount>
        <RSA_Encrypted_AES_key />
        <reqRefNo />
        <RSASignature />
        <serviceId>WSIFFTS0001</serviceId>
        <isReversePost>false</isReversePost>
        <userName />
        <password />
        <IPAddress />
        <orderingCustomerBBAN />
        <purposeofPayment_Value />
        <TransactionId />
        <SFTPId>0</SFTPId>
        <TotalAmount>0</TotalAmount>
        <IsAvailableBalanceCheck>false</IsAvailableBalanceCheck>
        <VerifyAllTransactionXml />
        <IsCorePostingEnabled>false</IsCorePostingEnabled>
    </WSI_OUT_IPI>
    <WSI_OUT_IPI>
        <orderingCustomerAccount />
        <orderingCustomerName />
        <orderingCustomerAddress />
        <beneficiaryAccount />
        <beneficiaryAccountName />
        <beneficiaryAccountAddress />
        <instructedAmount>0</instructedAmount>
        <remittanceInfo />
        <WSI_OUT_IPIID>835</WSI_OUT_IPIID>
        <WSI_OUT_IPI_INTER_ID>835</WSI_OUT_IPI_INTER_ID>
        <UID>0</UID>
        <beneficiaryInstitution />
        <purposeofPayment />
        <sendersRef />
        <benBankreferenceNo />
        <ftsReferenceNo />
        <middleWareReferenceNo />
        <currency />
        <messageType />
        <additionalField1 />
        <additionalField2 />
        <additionalField3 />
        <additionalField4 />
        <additionalField5 />
        <additionalField6 />
        <additionalField7 />
        <additionalField8 />
        <additionalField9 />
        <IsDirectEntry />
        <CreatedBy />
        <CreatedDate>2018-10-25T10:34:15.3825261+05:30</CreatedDate>
        <ModifiedBy />
        <ModifiedDate>2018-10-25T10:34:15.3825261+05:30</ModifiedDate>
        <DBErrorCode />
        <DBErrorDetails />
        <CreditNarration />
        <DebitNarration />
        <TranscactionCode />
        <CreditAccountNo />
        <DebitAccountNo />
        <ChannelID>0</ChannelID>
        <Amount>0</Amount>
        <ForceCredit>false</ForceCredit>
        <RetryCount>0</RetryCount>
        <DebitExchangeRate>0</DebitExchangeRate>
        <CreditExchangeRate>0</CreditExchangeRate>
        <AvailableBalance>0</AvailableBalance>
        <IsChargePost>false</IsChargePost>
        <ChargePostAmount>0</ChargePostAmount>
        <RSA_Encrypted_AES_key />
        <reqRefNo />
        <RSASignature />
        <serviceId>WSIFFTS0001</serviceId>
        <isReversePost>false</isReversePost>
        <userName />
        <password />
        <IPAddress />
        <orderingCustomerBBAN />
        <purposeofPayment_Value />
        <TransactionId />
        <SFTPId>0</SFTPId>
        <TotalAmount>0</TotalAmount>
        <IsAvailableBalanceCheck>false</IsAvailableBalanceCheck>
        <VerifyAllTransactionXml />
        <IsCorePostingEnabled>false</IsCorePostingEnabled>
    </WSI_OUT_IPI>
    <WSI_OUT_IPI>
        <orderingCustomerAccount />
        <orderingCustomerName />
        <orderingCustomerAddress />
        <beneficiaryAccount />
        <beneficiaryAccountName />
        <beneficiaryAccountAddress />
        <instructedAmount>0</instructedAmount>
        <remittanceInfo />
        <WSI_OUT_IPIID>834</WSI_OUT_IPIID>
        <WSI_OUT_IPI_INTER_ID>834</WSI_OUT_IPI_INTER_ID>
        <UID>0</UID>
        <beneficiaryInstitution />
        <purposeofPayment />
        <sendersRef />
        <benBankreferenceNo />
        <ftsReferenceNo />
        <middleWareReferenceNo />
        <currency />
        <messageType />
        <additionalField1 />
        <additionalField2 />
        <additionalField3 />
        <additionalField4 />
        <additionalField5 />
        <additionalField6 />
        <additionalField7 />
        <additionalField8 />
        <additionalField9 />
        <IsDirectEntry />
        <CreatedBy />
        <CreatedDate>2018-10-25T10:34:17.0225229+05:30</CreatedDate>
        <ModifiedBy />
        <ModifiedDate>2018-10-25T10:34:17.0225229+05:30</ModifiedDate>
        <DBErrorCode />
        <DBErrorDetails />
        <CreditNarration />
        <DebitNarration />
        <TranscactionCode />
        <CreditAccountNo />
        <DebitAccountNo />
        <ChannelID>0</ChannelID>
        <Amount>0</Amount>
        <ForceCredit>false</ForceCredit>
        <RetryCount>0</RetryCount>
        <DebitExchangeRate>0</DebitExchangeRate>
        <CreditExchangeRate>0</CreditExchangeRate>
        <AvailableBalance>0</AvailableBalance>
        <IsChargePost>false</IsChargePost>
        <ChargePostAmount>0</ChargePostAmount>
        <RSA_Encrypted_AES_key />
        <reqRefNo />
        <RSASignature />
        <serviceId>WSIFFTS0001</serviceId>
        <isReversePost>false</isReversePost>
        <userName />
        <password />
        <IPAddress />
        <orderingCustomerBBAN />
        <purposeofPayment_Value />
        <TransactionId />
        <SFTPId>0</SFTPId>
        <TotalAmount>0</TotalAmount>
        <IsAvailableBalanceCheck>false</IsAvailableBalanceCheck>
        <VerifyAllTransactionXml />
        <IsCorePostingEnabled>false</IsCorePostingEnabled>
    </WSI_OUT_IPI>
    <WSI_OUT_IPI>
        <orderingCustomerAccount />
        <orderingCustomerName />
        <orderingCustomerAddress />
        <beneficiaryAccount />
        <beneficiaryAccountName />
        <beneficiaryAccountAddress />
        <instructedAmount>0</instructedAmount>
        <remittanceInfo />
        <WSI_OUT_IPIID>833</WSI_OUT_IPIID>
        <WSI_OUT_IPI_INTER_ID>833</WSI_OUT_IPI_INTER_ID>
        <UID>0</UID>
        <beneficiaryInstitution />
        <purposeofPayment />
        <sendersRef />
        <benBankreferenceNo />
        <ftsReferenceNo />
        <middleWareReferenceNo />
        <currency />
        <messageType />
        <additionalField1 />
        <additionalField2 />
        <additionalField3 />
        <additionalField4 />
        <additionalField5 />
        <additionalField6 />
        <additionalField7 />
        <additionalField8 />
        <additionalField9 />
        <IsDirectEntry />
        <CreatedBy />
        <CreatedDate>2018-10-25T10:34:19.3425564+05:30</CreatedDate>
        <ModifiedBy />
        <ModifiedDate>2018-10-25T10:34:19.3425564+05:30</ModifiedDate>
        <DBErrorCode />
        <DBErrorDetails />
        <CreditNarration />
        <DebitNarration />
        <TranscactionCode />
        <CreditAccountNo />
        <DebitAccountNo />
        <ChannelID>0</ChannelID>
        <Amount>0</Amount>
        <ForceCredit>false</ForceCredit>
        <RetryCount>0</RetryCount>
        <DebitExchangeRate>0</DebitExchangeRate>
        <CreditExchangeRate>0</CreditExchangeRate>
        <AvailableBalance>0</AvailableBalance>
        <IsChargePost>false</IsChargePost>
        <ChargePostAmount>0</ChargePostAmount>
        <RSA_Encrypted_AES_key />
        <reqRefNo />
        <RSASignature />
        <serviceId>WSIFFTS0001</serviceId>
        <isReversePost>false</isReversePost>
        <userName />
        <password />
        <IPAddress />
        <orderingCustomerBBAN />
        <purposeofPayment_Value />
        <TransactionId />
        <SFTPId>0</SFTPId>
        <TotalAmount>0</TotalAmount>
        <IsAvailableBalanceCheck>false</IsAvailableBalanceCheck>
        <VerifyAllTransactionXml />
        <IsCorePostingEnabled>false</IsCorePostingEnabled>
    </WSI_OUT_IPI>
</FTS>


Что я уже пробовал:

 EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_SMSXml                        
               
 
              select               
                @p_Body =Body  ,
                @P_ToRecipient = ToRecipient   ,
                @p_SMSalertId = SMSalertId

              FROM OPENXML(@docHandle, N'/FTS',2)                                                     
                WITH                                           
                (              
                      Body varchar(Max) ,
                     ToRecipient varchar(20),
                     SMSalertId varchar(20)                
                )   


				                                                
                                                                              (
                                                                                  Reference,ModeID,TypeID,Application_ID,Bulk_ReferenceID,
                                                                                  ToRecipient,SysReferenceID,CIF,Contact_ID,
                                                                                  Template_ID,FromSenderID,Body,
                                                                                  Status,ScheduleDatetime,CreatedDatetime,
                                                                                  SentDatetime,Priority,
                                                                                  ErrorName,LanguageID,
                              AttachmentPath,MailSubject,
                                                                                  ISHTML,GeneratePDF,
                                                                                  PDFTemplate_ID,PDFTemplateVariable,
                                                                                  Response,CC,BCC
                           
                                                                              )
                                                                              values
                                                                              ('
                                                                                                                                    
                                                                                                                                    
                                                                                                                                    
                                                                                                                                   -- Declare @Values nvarchar(max)                                                                                                                 
                                                                              --set @Values =   
                                                                                                                                          +isnull(@p_Reference,'NULL')+','''+isnull(convert( varchar ,@p_ModeID),'NULL')+''','''+isnull(convert( varchar ,@p_TypeID ) ,'NULL')+''','''+isnull(convert( varchar ,@P_Application_ID) ,'NULL') +''','+
                                                                                                                                         isnull(convert( varchar ,@p_Bulk_ReferenceID) ,'NULL') + ','''+isnull(convert( varchar ,@P_ToRecipient) ,'NULL') +''','''+isnull(convert( varchar ,@p_sysReferenceId ),'NULL')+''','+isnull(convert( varchar ,@p_CIF)  ,'NULL')                      
                                                                                                                                           +','+isnull(convert( varchar ,@p_Contact_ID) ,'NULL')                    +',
                                                                                                                                         ''1'','''+isnull(convert( varchar ,Ltrim(rtrim(@p_FromSenderID ))) ,'NULL')+''','''+isnull(convert( varchar ,@p_Body  )  ,'NULL')+''','''+isnull(convert( varchar ,@P_Status   )   ,'NULL')
                                                                                                                                         +''','''+LEFT(CONVERT(VARCHAR, getdate(), 120),
10)+''','''+LEFT(CONVERT(VARCHAR, getdate(), 120), 10)+''',
                                                                                 '+isnull(convert( varchar ,@P_SentDatetime  ),'NULL') +','+isnull(convert( varchar ,@p_Priority   )  ,'''0''')+','+isnull(convert( varchar ,@P_ErrorName  )   ,'NULL')+','
                                                                                                                                         +isnull(convert( varchar ,@p_LanguageId  )  ,'NULL')+','+isnull(convert( varchar ,@p_AttachmentPath ) ,'NULL')+','''+isnull(convert( varchar ,@p_MailSubject )  ,'NULL')+''',
                                                                                 '+isnull(convert( varchar ,@p_ISHTML   )  ,'''0''')+','+isnull(convert( varchar ,@p_GeneratePDF )     ,'''0''') +','+isnull(convert( varchar ,@p_PDFTemplate_ID )  ,'NULL')+',
'+isnull(convert( varchar ,@p_PDFTemplateVariable),'NULL') +','+isnull(convert( varchar ,@p_Response ) ,'NULL')+','+isnull(convert( varchar ,@p_CC  ),'NULL') +', 
                                                                                 '+isnull(convert( varchar ,@p_BCC ),'NULL')+')'                                        
                                                                                                                                          
                                                                                                                                         EXECUTE (@Query)

1 Ответов

Рейтинг:
1

#realJSOP

0) Sql server, mysql, что?

1) ваши блоки кода отформатированы таким образом, что их невозможно прочитать, и у меня нет времени, чтобы исправить это.

2) Если вы хотите поместить данные в SQL из источника данных XML, то самый простой способ-начать с DataTable.ReadXML() метод.