Vb.Net read and extract data from XML

Question

I have been trying several threads to try and get my head around this. I have a customer xml file which contains x number of records. I have removed the non used data from the XML but need to get the values for the following Nm , InstAmt, Mmbid, and id. The xml file would uploaded and not saved onto disc. Any pointers would be helpful

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03">
  <CstmrCdtTrfInitn>
     <PmtInf>
      <Dbtr>
        <Nm>CUSTOMER NAME</Nm>
      </Dbtr>
      <CdtTrfTxInf>
        <Amt>
          <InstdAmt Ccy="GBP">220.00</InstdAmt>
        </Amt>
        <CdtrAgt>
          <FinInstnId>
            <ClrSysMmbId>
              <MmbId>010101</MmbId>
            </ClrSysMmbId>
          </FinInstnId>
        </CdtrAgt>
        <CdtrAcct>
          <Id>
            <Othr>
              <Id>02020202</Id>
            </Othr>
          </Id>
        </CdtrAcct>
      </CdtTrfTxInf>
    </PmtInf>
    <PmtInf>
      <Dbtr>
        <Nm>CUSTOMER TWO</Nm>
      </Dbtr>
      <CdtTrfTxInf>
        <Amt>
          <InstdAmt Ccy="GBP">1.00</InstdAmt>
        </Amt>
        <CdtrAgt>
          <FinInstnId>
            <ClrSysMmbId>
              <MmbId>101010</MmbId>
            </ClrSysMmbId>
          </FinInstnId>
        </CdtrAgt>
        <CdtrAcct>
          <Id>
            <Othr>
              <Id>20202020</Id>
            </Othr>
          </Id>
        </CdtrAcct>
      </CdtTrfTxInf>
    </PmtInf>
  </CstmrCdtTrfInitn>
</Document>

The following may be of interest: https://stackoverflow.com/a/73640395/10024425. For more examples, click my username and type _xml-serialization_ or _xml_ after the userid. — Tu deschizi eu inchid, Jul 10 '23 at 16:59

score 1 · Accepted Answer · answered Jul 10 '23 at 18:13

This looks like Xml format per ISO 20022. I have an Xml model for it. Mine was missing some of your elements but I have modified it for you,

Option Strict On

Imports System.Xml.Serialization

<System.SerializableAttribute(),
System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"),
System.Xml.Serialization.XmlRootAttribute([Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03", IsNullable:=False)>
Partial Public Class Document
    Public Property CstmrCdtTrfInitn As CstmrCdtTrfInitn
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class CstmrCdtTrfInitn
    Public Property GrpHdr As GrpHdr
    <XmlElement("PmtInf")>
    Public Property PmtInfs As List(Of PmtInf)
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class GrpHdr
    Public Property MsgId As String
    Public Property CreDtTm As String
        Get
            Return $"{CreDtTmSetValue:yyyy-MM-ddTHH:mm:sszzz}"
        End Get
        Set(value As String)
            Dim parsedValue As DateTime
            If DateTime.TryParse(value, parsedValue) Then CreDtTmSetValue = parsedValue
        End Set
    End Property
    Public Property NbOfTxs As Integer
    Public Property InitgPty As InitgPty
    <XmlIgnore>
    Public Property CreDtTmSetValue As DateTime
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class InitgPty
    Public Property Nm As String
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class PmtInf
    Public Property PmtInfId As String
    Public Property PmtMtd As String
    Public Property PmtTpInf As PmtTpInf
    <System.Xml.Serialization.XmlElementAttribute(DataType:="date")>
    Public Property ReqdExctnDt As Date
    Public Property Dbtr As Dbtr
    Public Property DbtrAcct As DbtrAcct
    Public Property DbtrAgt As DbtrAgt
    <System.Xml.Serialization.XmlElementAttribute("CdtTrfTxInf")>
    Public Property CdtTrfTxInf As List(Of CdtTrfTxInf)
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class PmtTpInf
    Public Property LclInstrm As LclInstrm
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class LclInstrm
    Public Property Prtry As String
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class Dbtr
    Public Property Nm As String
    Public Property PstlAdr As Adr
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class Adr
    Public Property AdrTp As String
    Public Property Dept As String
    Public Property SubDept As String
    Public Property StrtNm As String
    Public Property BldgNb As String
    Public Property PstCd As String
    Public Property TwnNm As String
    Public Property CtrySubDvsn As String
    Public Property Ctry As String
    <XmlElement("AdrLine")>
    Public Property AdrLine As String()
    Public Function ShouldSerializeCtry() As Boolean
        Return CBool(Not AdrLine?.Any())
    End Function
    Public Function ShouldSerializeAdrLine() As Boolean
        Return Not String.IsNullOrEmpty(Ctry)
    End Function
    Shared Function getCountryTwoLetterCode(country As String) As String
        If country.Length = 2 Then Return country
        Dim myCountry = ISO3166.Country.List.Where(Function(c) c.Name.Contains(country)).First()
        Return myCountry.TwoLetterCode
    End Function
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class DbtrAcct
    Public Property Id As DbtrAcctId
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class DbtrAcctId
    Public Property Othr As Othr
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class Othr
    Public Property Id As Integer = 38973836
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class DbtrAgt
    Public Property FinInstnId As FinInstnId
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class FinInstnId
    Public Property BIC As String
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class CdtTrfTxInf
    Public Property PmtId As PmtId
    Public Property Amt As Amt
    Public Property ChqInstr As ChqInstr
    Public Property Cdtr As Cdtr
    Public Property RltdRmtInf As RltdRmtInf
    Public Property RmtInf As RmtInf
    Public Property CdtrAgt As CdtrAgt
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class CdtrAgt
    Public Property FinInstnId As CdtrAgtFinInstnId
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class CdtrAgtFinInstnId
    Public Property ClrSysMmbId As ClrSysMmbId
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class ClrSysMmbId
    Public Property MmbId As String
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class PmtId
    Public Property EndToEndId As String
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class Amt
    Public Property InstdAmt As InstdAmt
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class InstdAmt
    <System.Xml.Serialization.XmlAttributeAttribute()>
    Public Property Ccy As String
    <System.Xml.Serialization.XmlTextAttribute()>
    Public Property Value As String
        Get
            Return $"{SetValue:F2}"
        End Get
        Set(value As String)
            Dim amount As Decimal
            If Decimal.TryParse(value, amount) Then SetValue = amount
        End Set
    End Property
    <XmlIgnore>
    Public Property SetValue As Decimal
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class ChqInstr
    Public Property ChqNb As String
    Public Property DlvrTo As DlvrTo
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class DlvrTo
    Public Property Nm As String
    Public Property Adr As Adr
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class Cdtr
    Public Property Nm As String
    Public Property PstlAdr As Adr
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class RltdRmtInf
    Public Property RmtLctnPstlAdr As RmtLctnPstlAdr
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class RmtLctnPstlAdr
    Public Property Nm As String = ""
    Public Property Adr As Adr
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class RmtInf
    <XmlElement("Ustrd")>
    Public Property Ustrds As List(Of Ustrd)
    <XmlElement("Strd")>
    Public Property Strds As List(Of Strd)
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class Ustrd
    <XmlText>
    Public Property Text As String
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class Strd
    Public Property RfrdDocInf() As RfrdDocInf
    Public Property RfrdDocAmt As RfrdDocAmt
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class RfrdDocInf
    Public Property Tp As New RmtInfStrdRfrdDocInfTP()
    Public Property Nb As String
    <System.Xml.Serialization.XmlElementAttribute(DataType:="date")>
    Public Property RltdDt As Date
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class RfrdDocAmt
    Public Property DuePyblAmt As DuePyblAmt
    Public Property DscntApldAmt As DscntApldAmt
    Public Property CdtNoteAmt As CdtNoteAmt
    Public Property RmtdAmt As RmtdAmt
End Class

''' <summary>
''' Credit Amount. Can occur multiple times when paying multiple invoices.
''' </summary>
<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Public Class CdtNoteAmt
    <System.Xml.Serialization.XmlAttributeAttribute()>
    Public Property Ccy As String
    <System.Xml.Serialization.XmlTextAttribute()>
    Public Property Value As String
        Get
            Return $"{SetValue:F2}"
        End Get
        Set(value As String)
            Dim amount As Decimal
            If Decimal.TryParse(value, amount) Then SetValue = amount
        End Set
    End Property
    <XmlIgnore>
    Public Property SetValue As Decimal
End Class

''' <summary>
''' Invoice Net Amount. Can occur multiple times when paying multiple invoices.
''' </summary>
<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Public Class RmtdAmt
    <System.Xml.Serialization.XmlAttributeAttribute()>
    Public Property Ccy As String
    <System.Xml.Serialization.XmlTextAttribute()>
    Public Property Value As String
        Get
            Return $"{SetValue:F2}"
        End Get
        Set(value As String)
            Dim amount As Decimal
            If Decimal.TryParse(value, amount) Then SetValue = amount
        End Set
    End Property
    <XmlIgnore>
    Public Property SetValue As Decimal
End Class

''' <summary>
''' Invoice Gross Amount. Can occur multiple times when paying multiple invoices.
''' </summary>
<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Public Class DuePyblAmt
    <System.Xml.Serialization.XmlAttributeAttribute()>
    Public Property Ccy As String
    <System.Xml.Serialization.XmlTextAttribute()>
    Public Property Value As String
        Get
            Return $"{SetValue:F2}"
        End Get
        Set(value As String)
            Dim amount As Decimal
            If Decimal.TryParse(value, amount) Then SetValue = amount
        End Set
    End Property
    <XmlIgnore>
    Public Property SetValue As Decimal
End Class

''' <summary>
''' Invoice Discount Amount. Can occur multiple times when paying multiple invoices.
''' </summary>
<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Public Class DscntApldAmt
    <System.Xml.Serialization.XmlAttributeAttribute()>
    Public Property Ccy As String
    <System.Xml.Serialization.XmlTextAttribute()>
    Public Property Value As String
        Get
            Return $"{SetValue:F2}"
        End Get
        Set(value As String)
            Dim amount As Decimal
            If Decimal.TryParse(value, amount) Then SetValue = amount
        End Set
    End Property
    <XmlIgnore>
    Public Property SetValue As Decimal
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class RmtInfStrdRfrdDocInfTP
    Public Property CdOrPrtry As New CdOrPrtry()
End Class

<System.SerializableAttribute(),
 System.ComponentModel.DesignerCategoryAttribute("code"),
System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=True, [Namespace]:="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03")>
Partial Public Class CdOrPrtry
    Public Property Cd As String
End Class

And you can Xml Serialization to deserialize the file into .net objects

Option Strict On

Imports System.IO
Imports System.Xml.Serialization

' ---------

Dim s As New XmlSerializer(GetType(Document))
Dim d As Document
Using fs As New FileStream("filename.xml", FileMode.Open)
    d = CType(s.Deserialize(fs), Document)
End Using
For Each p In d.CstmrCdtTrfInitn.PmtInfs
    Debug.Write($"Name: {p.Dbtr.Nm}")
    Debug.Write($", Amt: {p.CdtTrfTxInf.First().Amt.InstdAmt.Value} ({p.CdtTrfTxInf.First().Amt.InstdAmt.Ccy})")
    Debug.Write($", MemberID: {p.CdtTrfTxInf.First().CdtrAgt.FinInstnId.ClrSysMmbId.MmbId}")
    Debug.Write(Environment.NewLine)
Next

Output in your case

Name: CUSTOMER NAME, Amt: 220.00 (GBP), MemberID: 010101
Name: CUSTOMER TWO, Amt: 1.00 (GBP), MemberID: 101010

Requires NuGet package ISO3166 for country codes in the Xml model, if you don't need it, comment it

score 0 · Answer 2 · answered Jul 14 '23 at 00:25

So, this works:

Markup:

        <asp:GridView ID="GridView1" runat="server"
            CssClass="table" Width="60%">
        </asp:GridView>

And code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    LoadData()

End Sub

Sub LoadData()

    Dim ds As New DataSet
    Dim sFile = Server.MapPath("~/UpLoadFiles/test1.xml")
    ds.ReadXml(sFile)

    Dim rstResult As New DataTable
    For i = 1 To ds.Tables.Count - 1
        Dim dt As DataTable = ds.Tables(i)
        For Each f As DataColumn In dt.Columns
            If rstResult.Columns.Contains(f.ColumnName) = False Then
                Dim fNew As DataColumn = New DataColumn(f.ColumnName, f.DataType)
                rstResult.Columns.Add(fNew)
            End If
        Next
    Next

    For r = 0 To ds.Tables(1).Rows.Count - 1
        Dim NewRow As DataRow = rstResult.NewRow
        For t = 1 To ds.Tables.Count - 1
            For Each f As DataColumn In ds.Tables(t).Columns
                NewRow(f.ColumnName) = ds.Tables(t).Rows(r)(f.ColumnName)
            Next
        Next
        rstResult.Rows.Add(NewRow)
    Next
    GridView1.DataSource = rstResult
    GridView1.DataBind()

End Sub

And result:

Vb.Net read and extract data from XML

2 Answers2