How to parse multiple xmls and update in SQL?

Question

1.00/5 (2 votes)

See more:

I want to parse multiple xmls and update the status using sql. I will passing the Xml string from the front end level. Xml format is mentioned below and passing the primary key id to update multiple data in the xml.

I am able to parse single 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>

What I have tried:

 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)

Posted 24-Oct-18 19:12pm

ranio

Updated 25-Oct-18 0:02am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2018-10-25T00:03:00

Solution 1

0) Sql server, mysql, what?

1) Your code blocks are formatted in such a way as to be IMPOSSIBLE to read, and I don't have time to fix it.

2) If you want to put data into SQL from a XML data source, the absolute easiest way is to start with the DataTable.ReadXML() method.

Posted 25-Oct-18 0:03am

#realJSOP