VB.NET Loop Through XML Results

Question

I have redone this question as a few have suggested that it is difficult to understand what I am after so have cut down the question somewhat.

<?xml version="1.0"?>
<root>
    <succesfulResponses>
        <position>0</position>
        <response>
            <dln>BBUTU204250VS9VT</dln>
            <licence>
                <entitlements>
                    <code>A</code>
                    <validFrom/>
                    <validTo/>
                    <priorTo>false</priorTo>
                    <type>F</type>
                </entitlements>
                <entitlements>
                    <code>B</code>
                    <validFrom/>
                    <validTo/>
                    <priorTo>false</priorTo>
                    <type>F</type>
                </entitlements>
            </licence>
        </response>
    </succesfulResponses>
    <succesfulResponses>
        <position>1</position>
        <response>
            <dln>BTXRS755313Y99AT</dln>
            <licence>           
                <entitlements>
                    <code>A</code>
                    <validFrom>2003-02-28</validFrom>
                    <validTo>2043-05-30</validTo>
                    <priorTo>false</priorTo>
                    <type>P</type>
                </entitlements>
                <entitlements>
                    <code>AM</code>
                    <validFrom>2014-05-14</validFrom>
                    <validTo>2043-05-30</validTo>
                    <priorTo>false</priorTo>
                    <type>P</type>
                </entitlements>
            </licence>
            <httpStatusCode>200</httpStatusCode>
        </response>
    </succesfulResponses>
</root>

This is the XML I have returned, I submit several ID's to the service and it returns this XML.

If I send 2 ID's it returns 2 'successfulResponses' elements for each ID these ID's you can see in the child node 'dln' beneath 'response', you can see they are different.

'position' is simply which ID i submitted in the 'request' first. 'response' also has an element called 'licence' and several 'entitlement' elements.

I wish to insert these 'entitlements' into a datatable that is formatted in this way

    Results.EntitlementsTbl.Columns.Add(New DataColumn("Code", GetType(String)))
    Results.EntitlementsTbl.Columns.Add(New DataColumn("Valid From", GetType(String)))
    Results.EntitlementsTbl.Columns.Add(New DataColumn("Valid To", GetType(String)))
    Results.EntitlementsTbl.Columns.Add(New DataColumn("Prior To", GetType(String)))
    Results.EntitlementsTbl.Columns.Add(New DataColumn("Type", GetType(String)))
    Results.EntitlementsTbl.Columns.Add(New DataColumn("Driver", GetType(String)))

Each column relates to an node beneath each 'entitlements' element. I wish to add a new row to my datatable everytime I have an 'entitlements' element.

There is an additional column 'Driver', this needs to be populated with the number in 'position'.

So my the output to my table I would like to have the following:

As you can see the 'Driver' Column relates to the value of the 'position' node.

I hope this makes more sense than my previous. I have removed any of my own code I have stated I have done as its clearly not what was required.

Did you consider deserializing your JSON to .net objects instead? Seems like a lot of overhead to transform to Xml and query that. — Filburt
– Filburt, Commented Jan 18, 2016 at 15:27
@Filburt I wouldn't really have any idea where to begin there I'm afraid - I'm very much self taught in what I know, so its all a learning curve for me. — Lynchie
– Lynchie, Commented Jan 18, 2016 at 15:28
Check out VB.net JSON Deserialize - it should get you started. — Filburt
– Filburt, Commented Jan 18, 2016 at 15:29
@Filburt Thanks for that, I ideally would get around to figuring that out when I have more time. I just need to get this data into the tables at the moment. — Lynchie
– Lynchie, Commented Jan 18, 2016 at 15:32
It's not entirely clear what output you're trying to achieve here -could you add expected output from your XML doc? You're taking only the last dln, but using positions from all drivers, what entitlements do you want and where do you want them? — Dan Field
– Dan Field, Commented Jan 18, 2016 at 16:02

Dan Field · Accepted Answer · 2016-01-18 16:45:31Z

I'd heartily second the recommendation to deserialize the JSON directly to a CLR Object, and you might also find it easier to use the XDocument API, but if you really have to use the XmlDocument model for some reason, this code should help

Dim DriverNo As String

Using nodes As XmlNodeList = xmlDoc.DocumentElement.SelectNodes("/root/succesfulResponses")
    For Each node In nodes 
        Dim DLN As String = ""
        DriverNo = node.SelectSingleNode("position").InnerText
        DLN = node.SelectSingleNode("response/dln").InnerText.ToString()

        Using entitlements As XmlNodeList = node.SelectNodes("response/licence/entitlements")
            For Each entitlement In entitlements
                Dim code, validFrom, validTo, priorTo, type As String
                code = entitlement.SelectSingleNode("code").InnerText
                validFrom = entitlement.SelectSingleNode("validFrom").InnerText
                validTo = entitlement.SelectSingleNode("validTo").InnerText
                priorTo = entitlement.SelectSingleNode("priorTo").InnerText
                type = entitlement.SelectSingleNode("type").InnerText
                ' do what you need to with the variables here
            Next

        End Using

    Next
End Using

This way, you're iterating through each successfulResponses node, grabbing the DLN and the DriverNo, and then iterating through each entitlements node and getting the data from there. That will result in one row per entitlement.

The do what you need to with the variables here will be your actual code to insert into your datatable or whatever you're using.

Hi Dan, Top man that has sorted out what I needed, I will by all means look into JSON to a CLR Object but that is a new learning curve and something I don't have time for right now.

jdweng · Accepted Answer · 2016-01-18 16:15:37Z

Try xml Linq

Imports System.Xml
Imports System.Xml.Linq
Module Module1
    Const FILENAME As String = "c:\temp\test.xml"
    Sub Main()

        Dim doc As XDocument = XDocument.Load(FILENAME)

        Dim results = doc.Descendants("succesfulResponses").Select(Function(x) New With { _
           .position = CType(x.Element("position"), Integer), _
           .entitlements = x.Descendants("entitlements").Select(Function(y) New With { _
                               .code = CType(y.Element("code"), String), _
                               .validFrom = y.Elements("validFrom").Where(Function(z) z.Value.Length > 0).Select(Function(a) CType(a, DateTime)).FirstOrDefault(), _
                               .validTo = y.Elements("validTo").Where(Function(z) z.Value.Length > 0).Select(Function(a) CType(a, DateTime)).FirstOrDefault(), _
                               .priorTo = y.Elements("priorTo").Where(Function(z) z.Value.Length > 0).Select(Function(a) CType(a, Boolean)).FirstOrDefault(), _
                               .type = y.Elements("type").Where(Function(z) z.Value.Length > 0).Select(Function(a) CType(a, String)).FirstOrDefault() _
                               }).ToList() _
        }).ToList()

    End Sub

End Module

Whats wrong with the code. It is used all the time especially in c#. The code works so what is there to debug?

Collectives™ on Stack Overflow

VB.NET Loop Through XML Results

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related