Parse XML with metadata and data rows with vb.net

Question

I am new to xml parsing. I am trying to parse the following XML using VB.net

I have been doing a lot of reading, but I can't get it right

I am totally confused

...

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<!-- Generated by SMExport 4.99-->
<RECORDS>
    <METADATA>
        <FIELDS>
            <FIELD attrname="LAYBYE" fieldtype="i4"/>
            <FIELD attrname="TITLE" fieldtype="string" WIDTH="5"/>
            <FIELD attrname="INITS" fieldtype="string" WIDTH="7"/>
            <FIELD attrname="SURNAME" fieldtype="string" WIDTH="31"/>
            <FIELD attrname="COMPANYNAME" fieldtype="string" WIDTH="6"/>
            <FIELD attrname="EXPDATE" fieldtype="date"/>
            <FIELD attrname="BALANCE" fieldtype="r8" SUBTYPE="Money"/>
            <FIELD attrname="IDNUMBER" fieldtype="string" WIDTH="16"/>
            <FIELD attrname="Cellphone" fieldtype="string" WIDTH="21"/>
        </FIELDS>
        <PARAMS DEFAULT_ORDER="1" PRIMARY_KEY="1" LCID="1033"/>
    </METADATA>
    <RECORD>
        <ROW
          LAYBYE="1"
          TITLE="MR"
          INITS="J"
          SURNAME="DOE"
          EXPDATE="20190523"
          BALANCE="100"
          IDNUMBER="123"
          Cellphone="99999999"
        />
    </RECORD>
    <RECORD>
        <ROW
          LAYBYE="1"
          TITLE="MRS"
          INITS="JJ"
          SURNAME="DOE"
          EXPDATE="20190701"
          BALANCE="500"
          IDNUMBER="456"
          Cellphone="888888"
        />
    </RECORD>
 </RECORDS>

...

I am Expecting to Output the following to a Gridview | LAYBYE | TITLE | INITS | SURNAME | EXPDATE | BALANCE | IDNUMBER | |:------ |:------ |:----- |:------- |:--------:| -------:|:-------- | | 1 | MR | J | DOE | 20190523 | 100 | 123 | | 2 | MRS | JJ | DOE | 20190701 | 500 | 456 |
| | | | | | | |

I managed to get a CSV to GRIDVIEW

Do you need only the attributes from the <ROW> element? Can there be more than one <ROW> element, or perhaps more than one <RECORD> element? — Andrew Morton
– Andrew Morton, Commented Oct 13, 2022 at 12:40
The following may be helpful: stackoverflow.com/a/73640395/10024425 and stackoverflow.com/a/68513150/10024425 — It all makes cents
– It all makes cents, Commented Oct 13, 2022 at 14:36
What data are you trying to parse? There seems to be an extra > in </RECORD>>. — It all makes cents
– It all makes cents, Commented Oct 13, 2022 at 14:39
Good day. Thank you for the replies I am trying to Parse the Row Element It has multiple Rows — Henjane Pty Ltd
– Henjane Pty Ltd, Commented Oct 13, 2022 at 20:59

dbasnett · Accepted Answer · 2022-10-13 14:51:15Z

Without more detail the following is just a guess. You'll want to read up on XElement.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim myXML As XElement
    ' to load from uri
    'myXML = XElement.Load("path goes here") '<<<<<<<<<

    'for testing use literal
    '  note that the <?xml version ... statement is NOT used in literals
    myXML = <RECORDS>
                <METADATA>
                    <FIELDS>
                        <FIELD attrname="LAYBYE" fieldtype="i4"/>
                        <FIELD attrname="TITLE" fieldtype="string" WIDTH="5"/>
                        <FIELD attrname="INITS" fieldtype="string" WIDTH="7"/>
                        <FIELD attrname="SURNAME" fieldtype="string" WIDTH="31"/>
                        <FIELD attrname="COMPANYNAME" fieldtype="string" WIDTH="6"/>
                        <FIELD attrname="EXPDATE" fieldtype="date"/>
                        <FIELD attrname="BALANCE" fieldtype="r8" SUBTYPE="Money"/>
                        <FIELD attrname="IDNUMBER" fieldtype="string" WIDTH="16"/>
                        <FIELD attrname="Cellphone" fieldtype="string" WIDTH="21"/>
                    </FIELDS>
                    <PARAMS DEFAULT_ORDER="1" PRIMARY_KEY="1" LCID="1033"/>
                </METADATA>
                <RECORD>
                    <ROW
                        LAYBYE="1"
                        TITLE="MR"
                        INITS="J"
                        SURNAME="DOE"
                        COMPANYNAME="BAWAS"
                        EXPDATE="20190523"
                        BALANCE="100"
                        IDNUMBER="123"
                        Cellphone="99999999"
                    />
                </RECORD>
            </RECORDS>

    'get all FIELD 
    Dim ie As IEnumerable(Of XElement)
    'this assumes FIELD tags are in only one place
    ie = From el In myXML...<FIELD> Select el

    'example - look at attrname
    For Each el As XElement In ie
        Debug.WriteLine(el.@attrname)
    Next

    'get specific FIELD 
    ie = From el In myXML...<FIELD>
         Where el.@fieldtype = "string"
         Select el Order By Integer.Parse(el.@WIDTH)

    'show width
    For Each el As XElement In ie
        Debug.Write(el.@attrname & " ")
        Debug.WriteLine(el.@WIDTH)
    Next

    ie = From anEL In myXML.<RECORD>.<ROW>
         Select anEL

    For Each foo As XElement In ie
        Debug.WriteLine(foo.ToString)
    Next
End Sub

Collectives™ on Stack Overflow

Parse XML with metadata and data rows with vb.net

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related