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?

It appears you've miisspelled "CstrmPmtStsRpt". It should be "CstmrPmtStsRpt". Since that is a high level element and the query couldn't find it, the query couldn't find anything else.

A tip that may help. You can open the xml file in VS in another tab. This way you can copy and paste these long element names and get the spelling exact.

Thanks tinstaaf,

In the words of the great philosopher Homer - DOH! I will now go and repeatedly bang my head off my desk.

Hi tinstaff,

I tried that but no luck..
I tried just to get just the CstmrPmtStsRpt children out as a test:
Dim FileErrs = From Document In doc.Descendants From CstmrPmtStsRpt In Document.Elements("CstmrPmtStsRpt") Select CstmrPmtStsRpt.Elements
But I still get enumeration yielded no results

Ok,
I have it now, using an example from elsewhere - I wasn't taking the namespace into account.

Dim ns As XNamespace = "urn:iso:std:iso:20022:tech:xsd:pain.002.001.03" 'The xml namespace
Dim FileErrs = doc.Descendants(ns + "OrgnlPmtInfAndSts").Elements(ns + "TxInfAndSts")
For each FileErr in FileErrs
     '/OrgnlTxRef/Amt/InstdAmt
     dblAmount = FileErr.Element(ns + "OrgnlTxRef").Element(ns + "Amt").Element(ns + "InstdAmt").Value
     '/OrgnlTxRef/MndtRltdInf/MndtId
     strMandateReference = FileErr.Element(ns + "OrgnlTxRef").Element(ns + "MndtRltdInf").Element(ns + "MndtId").Value
     '/OrgnlTxRef/MndtRltdInf/DtOfSgntr
     dtDateMandateSigned = FileErr.Element(ns + "OrgnlTxRef").Element(ns + "MndtRltdInf").Element(ns + "DtOfSgntr").Value
     strCustomerBIC = FileErr.Element(ns + "OrgnlTxRef").Element(ns + "DbtrAgt").Element(ns + "FinInstnId").Element(ns + "BIC").Value
     strCustomerIBAN = FileErr.Element(ns + "OrgnlTxRef").Element(ns + "DbtrAcct").Element(ns + "Id").Element(ns + "IBAN").Value
     strCustomerAccountName = FileErr.Element(ns + "OrgnlTxRef").Element(ns + "Dbtr").Element(ns + "Nm").Value
     strTransactionType = FileErr.Element(ns + "OrgnlTxRef").Element(ns + "PmtTpInf").Element(ns + "SeqTp").Value
     strEndToEndID = FileErr.Element(ns + "OrgnlEndToEndId").Value
     strReasonCode = FileErr.Element(ns + "StsRsnInf").Element(ns + "Rsn").Element(ns + "Cd").Value
     strReason = _ErrorCodes.Item(strReasonCode).Trim
     DT.Rows.Add(dblAmount, strMandateReference, dtDateMandateSigned, strCustomerBIC, strCustomerIBAN, strCustomerAccountName, strTransactionType, strEndToEndID, strReasonCode, strReason)
Next
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.