Hi All,
I'm fairly new to using Linq but I thought I had a handle on it, apparently not though. I'm processing an XML file sent out as an error report by a third party. I have no control over this file and it has been developed to an industry standard so I can not get the format changed even if I want to.
Anyway here is a "cut down" sample of the file, sorry about the size but I've only put things I need to query on.:
<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.002.001.03" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<CstmrPmtStsRpt>
<GrpHdr>
<!-- I dont use this area so I wont type the elements here-->
</GrpHdr>
<OrgnlGrpInfAndSts>
<OrgnlMsgId>MCY20131</OrgnlMsgId>
<OrgnlMsgNmId>pain.008.001.02</OrgnlMsgNmId>
<OrgnlNbOfTxs>10</OrgnlNbOfTxs>
<OrgnlCtrlSum>6600.00</OrgnlCtrlSum>
<StsRsnInf>
<Rsn>
<!-- If this path exists and there is a value in Cd then the whole file was rejected.-->
<Cd>MD03</Cd>
</Rsn>
</StsRsnInf>
</OrgnlGrpInfAndSts>
<OrgnlPmtInfAndSts>
<!-- There could be multiple TxInfAndSts depending on number of transactions imported -->
<TxInfAndSts>
<!-- Need the OrgnlEndToEndId value to link back to Import Record-->
<OrgnlEndToEndId>20131</OrgnlEndToEndId>
<StsRsnInf>
<Rsn>
<!-- Reason for rejection of transaction-->
<Cd>AC01</Cd>
</Rsn>
</StsRsnInf>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="EUR">660.00</InstdAmt>
</Amt>
<PmtTpInf>
<SeqTp>FRST</SeqTp>
</PmtTpInf>
<MndtRltdInf>
<MndtId>0008Customer 801012013</MndtId>
<DtOfSgntr>2013-01-01</DtOfSgntr>
</MndtRltdInf>
<Dbtr>
<Nm>Customer 8</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>IE20BOFI90003333817105</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>BOFIIE20</BIC>
</FinInstnId>
</DbtrAgt>
</OrgnlTxRef>
</TxInfAndSts>
</OrgnlPmtInfAndSts>
</CstmrPmtStsRpt>
</Document>
So I've written a sub that takes in the filepath to the XMl file and then produces either an overall error code or populates a datatable for each transaction error that I place in a grid.
First thing I do is load in the XML to an XDocument Dim doc = XDocument.Load(FileName.Trim) on the Debug I can see the XML code...
Next I want to check for an overall file rejection so I do this:
'If entry at CstmrPmtStsRpt\OrgnlGrpInfAndSts\StsRsnInf\Rsn\Cd whole file failed for reason code
Dim FileRejection = From Document In doc.Descendants _
From CstrmPmtStsRpt In Document.Elements("CstrmPmtStsRpt") _
From OrgnlPmtInfAndSts In CstrmPmtStsRpt.Elements("OrgnlPmtInfAndSts") _
From StsRsnInf In OrgnlPmtInfAndSts.Elements("StsRsnInf") _
From Rsn In StsRsnInf.Elements("Rsn") _
From Cd In Rsn.Elements("Cd") _
Select CStr(Cd)
If FileRejection.Count <> 0 Then
'_ErrorCodes is a dictionary of the error codes, TheFileRejection is Global string
TheFileRejection = _ErrorCodes.Item(FileRejection.First)
Exit Sub
Else
TheFileRejection =""
End If
I get "Enumeration yielded no results" on the FileRejection Linq Query in Debug but I may not have any values here so that's OK.
Next, having got past the FileRejection Check, I create a new Datatable and then put the individual transaction failures in.
Dim DT as New DataTable
'add table columns
DT.Columns.Add(New DataColumn("dblAmount", System.Type.GetType("System.Decimal")))
DT.Columns.Add(New DataColumn("strMandateReference", System.Type.GetType("System.String")))
DT.Columns.Add(New DataColumn("dtDateMandateSigned", System.Type.GetType("System.DateTime")))
DT.Columns.Add(New DataColumn("strCustomerBIC", System.Type.GetType("System.String")))
DT.Columns.Add(New DataColumn("strCustomerIBAN", System.Type.GetType("System.String")))
DT.Columns.Add(New DataColumn("strCustomerAccountName", System.Type.GetType("System.String")))
DT.Columns.Add(New DataColumn("strTransactionType", System.Type.GetType("System.String")))
DT.Columns.Add(New DataColumn("strEndToEndID", System.Type.GetType("System.String")))
DT.Columns.Add(New DataColumn("strReasonCode", System.Type.GetType("System.String")))
DT.Columns.Add(New DataColumn("strReason", System.Type.GetType("System.String")))
'Query XML for Transaction Failures
Dim FileErrs = From Document In doc.Descendants _
From CstrmPmtStsRpt In Document.Elements("CstrmPmtStsRpt") _
From OrgnlPmtInfAndSts In CstrmPmtStsRpt.Elements("OrgnlPmtInfAndSts") _
From TxInfAndSts In OrgnlPmtInfAndSts.Elements("TxInfAndSts") _
Select New With {.Amount = CDbl(TxInfAndSts.Element("OrgnlTxRef").Element("Amt").Element("InstdAmt").Value), _
.MandateReference = TxInfAndSts.Element("OrgnlTxRef").Element("MndtRltdInf").Element("MndtId").Value, _
.dtDateMandateSigned = CDate(TxInfAndSts.Element("OrgnlTxRef").Element("MndtRltdInf").Element("DtOfSgntr").Value), _
.CustomerBIC = TxInfAndSts.Element("OrgnlTxRef").Element("DbtrAgt").Element("FinInstnId").Element("BIC").Value, _
.CustomerIBAN = TxInfAndSts.Element("OrgnlTxRef").Element("DbtrAcct").Element("Id").Element("IBAN").Value, _
.CustomerAccountName = TxInfAndSts.Element("OrgnlTxRef").Element("Dbtr").Element("Nm").Value, _
.strTransactionType = TxInfAndSts.Element("OrgnlTxRef").Element("PmtTpInf").Element("SeqTp").Value, _
.strEndToEndID = TxInfAndSts.Element("OrgnlEndToEndId").Value, _
.strReasonCode = TxInfAndSts.Element("StsRsnInf").Element("Orgtr").Element("Rsn").Element("Cd").Value}
For Each FileErr in FileErrs
With FileErr
DT.Rows.Add(.Amount, .MandateReference, .dtDateMandateSigned, .CustomerBIC, .CustomerIBAN, .CustomerAccountName, _
.strTransactionType, .strEndToEndID, .strReasonCode, _ErrorCodes.Item(.strReasonCode))
End With
Next
However, I know for sure there is at least one failed transaction but again I get "Enumeration yielded no results" on the debug. What am I missing / doing wrong?
