Linq XML to DataGrid

[charlie]

Hello Community,

My application gets XML information back from web services. I want to put that information in to a data grid. I have a big problem, however. The out put looks like this

TicketNumber    |Summary            |Description 
820                   820                    820           
Test Summary     Test Summary      Test Summary 
Test Description   Test Description   Test Description 

821                    821                    821
Test Summary     Test Summary      Test Summary 
Test Description   Test Description   Test Description

the output should look like this -->

TicketNumber    |Summary            |Description 
820                  Test Summary       Test Description                        
821                  Test Summary       Test Summary

Here a Code Behind look

 Try
            Dim xData As XElement
            xData = XElement.Parse(data)

            Dim xDataFile As XElement = xData
            Dim xDoc As XDocument = New XDocument()

            Dim myAttibuteName = From AName In xDataFile...<Attribute> _
                                 Select AName.<AttrName>.Value, AName.<AttrValue>.Value


            For Each ANameVal In myAttibuteName

                Response.Write("<br/>")
                Response.Write(ANameVal.AttrName.ToUpper & ": " & vbTab & ANameVal.AttrValue)
                Response.Write(vbCrLf)
                Response.Write("<br /> <br />")
                myAttibuteName.Distinct()

            Next
            DataGridView1.DataSource = myAttibuteName.ToList
            DataGridView1.DataMember = myAttibuteName.ToString
            DataGridView1.DataBind()






        Catch ex As XmlException
        End Try

I am completely new to this, but I learn fast. What am I doing wrong?

 

[charlie]

Help

Timh,

Can you help me?


I have a big problem. I am really stuck.


Here is the xml file I am working with:

<?xml version="1.0" encoding="UTF-8"?>
<UDSObjectList>
  <UDSObject>
    <Handle>cr:12345</Handle>
    <Attributes>
      <Attribute DataType="2002">
        <AttrName>ref_num</AttrName>
        <AttrValue>334046</AttrValue>
      </Attribute>
      <Attribute DataType="2002">
        <AttrName>summary</AttrName>
        <AttrValue>Test</AttrValue>
      </Attribute>
      <Attribute DataType="2002">
        <AttrName>description</AttrName>
        <AttrValue>Test</AttrValue>
      </Attribute>
      <Attribute DataType="2005">
        <AttrName>type</AttrName>
        <AttrValue>I</AttrValue>
      </Attribute>
    </Attributes>
  </UDSObject>
  <UDSObject>
    <Handle>cr:134567</Handle>
    <Attributes>
      <Attribute DataType="2002">
        <AttrName>ref_num</AttrName>
        <AttrValue>999040</AttrValue>
      </Attribute>
      <Attribute DataType="2002">
        <AttrName>summary</AttrName>
        <AttrValue>test</AttrValue>
      </Attribute>
      <Attribute DataType="2002">
        <AttrName>description</AttrName>
        <AttrValue>test</AttrValue>
      </Attribute>
      <Attribute DataType="2005">
        <AttrName>type</AttrName>
        <AttrValue>I</AttrValue>
      </Attribute>
    </Attributes>
  </UDSObject>
  <UDSObject/>
</UDSObjectList>

I would like to retreive all the Element( "AttrValue")
and place it in a gridview But I cannot achieve this.

I need 4 columns

ref_number| Summary | description | Type

I keep getting everything in one column...

I can interate through and retreive ref_numbers but I cannot get any columns

Dim q = (From Xfile In From Xfile In xDataFile...<Attribute> _
                                   Where Xfile.Element("AttrName").Value = "ref_num"  _ 
Select Xfile><AttrValue>.Value)

Dim q2 = (From Xfile In From Xfile In xDataFile...<Attribute> _
                                   Where Xfile.Element("AttrName").Value = "summary"  _ 
Select Xfile><AttrValue>.Value)


Dim q3 = (From Xfile In From Xfile In xDataFile...<Attribute> _
                                   Where Xfile.Element("AttrName").Value = "description"  _ 
Select Xfile><AttrValue>.Value)

Dim q4 = (From Xfile In From Xfile In xDataFile...<Attribute> _
                                   Where Xfile.Element("AttrName").Value = "type"  _ 
Select Xfile><AttrValue>.Value)

But I don't know where to go from here. I tired to put everything in to a list

Dim list as List (Of String)
list.add(q)
...

gridview1.datasource = list
gridview1.databind()

But this does not work. I tried various other ways, like created a class and set properties nothing, I am lost.

 

[charlie]

I really need help. Posting was not my first resort. I have done a ton of research, but I am not seeing things clearly right now. So I am asking for the communities help with this. Please.

 

[timh]

Hi Charlie,

I am a newbie to VB.NET and XML, so can only give a beginner's perspective.

All my reading suggests that using attributes in XML is not ideal. This certainly seems to be the case when trying to read XML into a datagridview (DGV) control!

My book on XML suggests that reformatting an XML file to suit your application is a very common task, as one data file produced by one application may be used for very different purposes by another.

So, my suggestion to you would be as follows. Create an XSL stylesheet to transform your input XML into another XML format, where each attribute becomes a separate element. Then read the modified XML file into a dataset and hey presto, your DGV should display just fine!

I don't entirely follow your existing XML, but consider this file...

<loc>
   <waypoint>
     <name id="GCXXX">Test point</name>
     <coord lat="1234" lon="5678"/>
     <type>Normal</type>
     <link text="Details">http://.......</link>
   </waypoint>
   <waypoint>
     .....
   </waypoint>
</loc>

I wanted each row of my DGV to represent a <waypoint> element, which it wouldn't do as it stood (because of the attributes), so I converted the above XML to...

<collection>
   <waypoint>
      <id>GCXXX</id>
      <name>Test point</name>
      <lat>1234</lat>
      <lon>5678</lon>
      <type>Normal</type>
      <linktext>Details</linktext>
      <link>http://.......</link>
   </waypoint>
   <waypoint>
       ......
   </waypoint>
</collection>

This displays fine by reading the transformed XML into a dataset (myDataset) and displaying the "waypoint" table.

DataGridView1.Datasource=myDataset
DataGridView1.Tables="waypoint"

I get a table with columns as follows:

    id    |    name    |    lat    |    lon    |    type    |    linktext    |    link    
 GCXXX      Test point     1234        5678         Normal        Details        http://....

Try and work out a suitable structure for your XML and then write an XSL stylesheet to do the transformation. It's really easy.

Hope this helps.

Regards,

Tim

 

[charlie]

I will try it soon

Hi Tim,

Thanks for the reply. I read the same thing about attributes, however, is the nodes in my XML file considered attributes? I was treating them as Elements...My XML File is really strange.

I will try the transformation in the next day or so to share my experience with you. It kind of bothers me that I cannot really make sense of the xml results.


Does "<Attributes><Attribute DataType=#>" represent Element.ChildElement?
<Attributes>
<Attribute DataType="2002">
<AttrName></AttrName>
<AttrValue></AttrValue>
</Attribute>
<Attribute DataType="2002">
<AttrName></AttrName>
<AttrValue></AttrValue>
</Attribute>
</Attributes>


Sorry, I been working for a few weeks to get the information displayed to our users. This XML comes from a web service. I am not sure it I should save it to a file because of other issues that may present.

I really appreciate your help. I am interning at a company that really don't do development. I am creating a web application that users there Service Desk XML WebService. It is going well, but it is rough because I don't have other developers to talk with...

Best,
Charles

 

[timh]

Charles,

I find the XML structure you are working with a little confusing, as there are elements called "Attribute". When XML talks about attributes, it is referring to the bits within the element tag after the "=" sign.

Looking at your XML structure, you have an element called "Attributes" which has child elements called "Attribute".

Each "Attribute" element has two child elements: "AttrName" and "AttrValue".

DataType is an attribute of the Attribute element, or in other words, the attribute of the child element of "Attributes". See what I mean about the element names being confusing?!?

It would make matters a lot easier if the original "Attribute" element had three child elements: "DataType", "AttrName" and "AttrValue" and I can't see why it has been created the way it has, though I'm sure someone had their reasons. I would certainly be looking at transforming it to that structure if I were you.

I was rather hoping someone else might chip in as I feel it is a case of the blind leading the blind here - I am certainly not an expert and would hate to lead you astray!! Hopefully there is some food for thought though.

Let me know how you get on.

Regards,

Tim

 

[charlie]

Your the best

Thank you Tim,

I thank you for your help. I think you are doing a terrific job. Your reply made me chuckled, with all that talk about Attributes and Attribute with an Attribute ( I am not quoting you. That is how I read it). I totally get you. Sometimes, I just need to let go and and restructure, LOL.

I am going to create a class that structures the xml in a way that makes sense, so I can then use it in my program. I will post my excitement or frustration, very soon.

Charles

 

[timh]

Sorry, I been working for a few weeks to get the information displayed to our users. This XML comes from a web service. I am not sure it I should save it to a file because of other issues that may present.

Charles,

I should have said that you don't need to save it to a new file as I believe you can cache transforms in memory by using the MemoryStream class with the XslTransform class.

So instead of transforming the original to a file...

Xsl.Transform("..\input.xml","..\output.xml")

You would use something along the lines of...

Xsl.Transform("..\input.xml",myMemoryStream)

Don't quote me on that code exactly, as I've not got around to trying that in my application, but you get the idea.

Regards,

Tim

 

[charlie]

Okay. Thanks