Question XML and combining various elements/nodes/attributes

[Eksbee]

I have an XML document which contains the following code. (I do not have the option to change the format of this XML, i must use it in its current format)

<issuers>
<issuer name="20th Century Industries" id="176320" industry="30" country="USA" date="2003-02-17"/>
<issuer name="21st Century Insurance Company" id="176320" industry="04" country="USA" date="2010-06-13"/>
</issuers>

<agencies>
<agency name="English Bond Rating Service" id="1"/>
<agency name="American Bond Rating Service" id="2"/>
</agencies>

<ratings>
<rating issuer-id="176320" agency-id="1" long-grade="Baa3" short-grade="" lp="0" sp="0" long-watch="" short-watch="" date="2006-09-18"/>
<rating issuer-id="176320" agency-id="2" long-grade="BBB+" short-grade="" lp="0" sp="0" long-watch="" short-watch="" date="2010-09-13"/>
</ratings>

As you can see the XML is split up into 3 distinct sections, ISSUERS, AGENCIES and RATINGS, In short Ratings are given to issuers by various agencies. (i.e all the sections and id numbers link up link up)


The code on my button is as follows:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xmldoc As New XmlDataDocument()
        Dim xmlnode As XmlNodeList
        Dim i As Integer
        Dim str As String
        Dim fs As New FileStream("C:\MyXMLfile.xml", FileMode.Open, FileAccess.Read)
        xmldoc.Load(fs)
        xmlnode = xmldoc.GetElementsByTagName("rating")

        For i = 0 To xmlnode.Count - 1
            str = xmlnode(i).Attributes("issuer-id").Value & " | " & xmlnode(i).Attributes("agency-id").Value & " | " & xmlnode(i).Attributes("long-grade").Value & " | " & xmlnode(i).Attributes("short-grade").Value & " | " & xmlnode(i).Attributes("date").Value
            MsgBox(str)
        Next
    End Sub

This iterates through the Ratings and outputs the the attributes in the ratings:

e.g 176320 | 1 | Baa3 | 0 | 2006-09-18

as you can see "176320" means absolutely nothing, its not useful to anyone using the application. Instead of outputting 176320 I wish to look up the <issuers> portion of the XML and find the id "176320" and then find its name value. (i would also like to lookup the agency that has id = 1)

so my output should be:

20th Century Industries | English Bond Rating Service | Baa3 | 0 | 2006-09-18

Hope this is clear, all i'm trying to do is link up the ID's so that the information being displayed actually makes sense to the end user.

 

[ss7thirty]

Not quite sure what the point of those ID's are but what are you having trouble doing? Are you getting some sort of error. I never see you referencing "name" or "id" in your code? It is good to ask a precise question when posting also.

 

[Eksbee]

Im sorry, i tried to explain it as best as I could, but its difficult!!! I dont know how else to explain it.

<ratings>
<rating issuer-id="176320" agency-id="1" long-grade="Baa3" short-grade="" lp="0" sp="0" long-watch="" short-watch="" date="2006-09-18"/>
<rating issuer-id="176321" agency-id="2" long-grade="BBB+" short-grade="" lp="0" sp="0" long-watch="" short-watch="" date="2010-09-13"/>
<ratings>

my code currrently works, at the moment the code ignores everything in the XML file apart from the above - it outputs the data above, in a message box.

example of output:
176320 | 1 | Baa3 | | 2006-09-18
176321 | 2 | BBB+ | | 2010-09-13

->>> however outputting that is absolutely useless to me. Since 176320 and 176321 mean absolutely nothing to an end user, that is using the application.


what I would like to, instead of outputting these values I want to USE the issuer-id value (xmlnode(i).Attributes("issuer-id").Value) look up the issuers section of the XML and find the corresponding "id" value (SEE BELOW):

<issuers>
<issuer name="20th Century Industries" id="176320" industry="30" country="USA" date="2003-02-17"/>
<issuer name="21st Century Insurance Company" id="176321" industry="04" country="USA" date="2010-06-13"/>
</issuers>

When the "row" containing the "id" is found, I want to USE the "name" attribute and output that instead.

example of final output:
20th Century Industries | 1 | Baa3 | | 2006-09-18
21st Century Insurance Company | 2 | BBB+ | | 2010-09-13

I really hope somebody can follow this through and help me.

 

[ss7thirty]

Have functions like this:

Function GetIssuer(id as string, xmldoc as XmlDocument) as string
'insert code to read the issuers element of the document
'loop through elements of issuers
'loop through atrributes match id while storing others you wish to keep in memory and return value if you have a match
End Fucntion

Trying using breakpoints and the immediate window to see what each of the different XML objects contain / return. But you should be able to create a few different functions that return your desired values based on an id that you pass to the function as well as the XML Document. I am not too familiar with attributes mainly because I have heard you should avoid using them in most cases because of restrictions. Those fields should intead be elements of the issuer rather than attributes. The following article may provide some insight. They show how each of the different XML structures works and an example of when using attributes is a good idea and it seems similar to what you are doing but I am not sure of the XML in your application. http://www.w3schools.com/DTD/dtd_el_vs_attr.asp

I thought this to be a great example of a way that you could simplify your xml especially for looping through the elements for code purposes.

<note date="12/11/2002">
  <to>Tove</to>
  <from>Jani</from>
  <heading>Reminder</heading>
  <body>Don't forget me this weekend!</body>
</note>

A date element is used in the second example:

<note>
  <date>12/11/2002</date>
  <to>Tove</to>
  <from>Jani</from>
  <heading>Reminder</heading>
  <body>Don't forget me this weekend!</body>
</note>

An expanded date element is used in the third: (THIS IS MY FAVORITE):

<note>
  <date>
    <day>12</day>
    <month>11</month>
    <year>2002</year>
  </date>
  <to>Tove</to>
  <from>Jani</from>
  <heading>Reminder</heading>
  <body>Don't forget me this weekend!</body>
</note>

 

[Eksbee]

Yup i kind of know the logic behind what I need to do, I just don't know how to do it, and thats where I need help. The XML document is not controlled by me, I cant make any alterations to it.

I've been looking at getelementbyid - XmlDocument.GetElementById Method (System.Xml) but its not working. I would love a working example.

 

[JohnH]

The easy solution is to use Xpath expressions to get the related node:

For Each rating As XmlNode In xmldoc.SelectNodes("//rating")
    Dim issuer = xmldoc.SelectSingleNode(String.Format("//issuer[@id='{0}']", rating.Attributes("issuer-id").Value))
    
Next

 

[JohnH]

Linq to Xml is also an option, though dashes in identifiers don't play well:

Dim doc = XDocument.Load("data.xml")        
Dim ratings = From rating In doc...<rating> _
              Join issuer In doc...<issuer> _
              On rating.Attribute("issuer-id").Value Equals issuer.@id

For Each result In ratings

Next