XML to dataset - problem finding what datarow contains a datatable

Question

Sorry if the description is poor, but I don't know how else to put this... But here is an example of the XML structure

<?xml version=”1.0” encoding=”UTF-8”>
<Response xmlns="http://www.blah.com">
  <searchResult>
    <info>
      <firstName>John</firstName>
      <lastName>Doe</lastName>
      <totalCharges>100.00</totalCharges>
      <nonPaymentCode>99999</nonPaymentCode>
    </info>
    <info>
      <firstName>Susan</firstName>
      <lastName>Doe</lastName>
      <totalCharges>1000.00</totalCharges>
      <errorCodes>
        <errorCode>12345</errorCode>
      </errorCodes>
    </info>
    <info>
      <firstName>Peter</firstName>
      <lastName>Doe</lastName>
      <totalCharges>10.00</totalCharges>
      <errorCodes>
        <errorCode>12345</errorCode>
        <errorCode>54321</errorCode>
        <errorCode>85246</errorCode>
      </errorCodes>
    </info>
  </searchResult>
</claimInquiryResponse>

I have transformed this into a dataset in order to access the information as a datatable. But my problem is trying to get the errorCodes. I was trying to figure this out by checking the relationship of the tables. Because I have to figure out what error codes are associated with what person, in order to display them properly. I cannot control the XML structure, and everything is optional in order to reduce excess bandwidth so changing it is not an option at this point. But here is an example of what I have done:

For Each rel As DataRelation In ds.Tables(i).ChildRelations
    If rel.Nested Then
        Dim temp As New DataTable
        temp = rel.ChildTable
        For Each relationship As DataRelation In temp.ChildRelations
            For Each row As DataRow In relationship.ChildTable.Rows
                For Each column As DataColumn In relationship.ChildTable.Columns
                    rowValues &= column.ColumnName & "-" & row(column.ColumnName) & "-" & relationship.RelationName & vbCrLf
                Next
            Next
        Next
    End If
Next

This gives me the actual values of the data inside of errorCodes table. but I can't figure out how to link it back to what row/person it was associated with. The indexes the relationship generate are built off of their occurance (for instance the error codes for susan will be 0 and for peter it will be 1), not relating to their location in the XML.

I am either doing this wrong or looking at this data incorrectly. I know I can use a XML reader to get all this, but the data is already in the dataset, and I feel like learning something new. so only suggest it as a last resort please.

thanks in advance!

Answer 1

I ended up just reading through the XML checking the node name and building a separate table for the error codes, and when I found the end element for info I spat out the tables individually.