如何解析多个xmls并在SQL中更新？ [英] How to parse multiple xmls and update in SQL?
本文介绍了如何解析多个xmls并在SQL中更新？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想解析多个xmls并使用sql更新状态。我将从前端级别传递Xml字符串。下面提到了Xml格式并传递了主键id以更新xml中的多个数据。
我能够解析单个xml。
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)
推荐答案
0）sql server，mysql，什么？
1）您的代码块的格式是无法读取的，我没有时间修复它。
2）如果要将数据从XML数据源放入SQL，绝对最简单的方法是从DataTable.ReadXML（）方法开始。
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 theDataTable.ReadXML()method.
这篇关于如何解析多个xmls并在SQL中更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
