[RESOLVED] table to xml advice/help?

[jculpepper]

Not sure if this is the best place to post these questions but I need some advice.
I'm still new with vb and .asp so any help would be appreciated.
My questions are:
I have a table (say table1) that has a list of addresses in it. I need to pass these addresses to a batch geocoder (google, bing, etc...). From what I've read xml is the format the addresses need to be in to pass this on.
what would be the best way to convert the table to an xml document?
I've successfully converted the addresses in the table using :

Dim addressXml As New System.IO.StringWriter
            table1.WriteXml(addressXml, XmlWriteMode.WriteSchema, True)

but the resulting xml is not formated very well.
can I alter the formating somehow?

I've also seen others using xmltextwriter. Is this a better/more efficent way?

[gep13]

Hello,

Let's just take a step back, and make sure we are talking about the same things.

I am assuming (based on the call to WriteXml) that table is a DataTable, correct? If so, am I right in saying that you have populated this DataTable using some code to go off to the Database Table and .Fill from there?

If so, you might want to think about re-thinking this approach.

If you are not actually going to use this DataTable for anything other than creating an XML file, then creating an in memory version of the Database Table in not required.

Instread, you might want to think about using the DataReader object to loop through each row in the Database Table, and as you are looping, create the necessary XML file. This can either be done be creating an in memory XmlDocument and adding nodes to it (however, this has a performance penalty if the Database Table contains lots of rows) or you might want to think about using the XmlTextWriter as you have already alluded to.

Gary

[jculpepper]

Thanks for the info. Your advice to use the datareader makes sense to me. I don't have any reason to fill the table other then to create a xml file(to use in a batch geocoder) and populate a grid view on a web page. I don't expect the database table to contain more than 4 to 5 columns and a max of 50 rows. I'm going to see how the datareader works out. thanks again.

[gep13]

Not a problem at all.

Let me know if you have any additional questions.

Gary

[jculpepper]

Gary I've got another question if you have some time. I've got the following code to parse geocoords from bing. it will give me the latitude but not the longitude. If I comment out the latitude portion of the code it will return the longitude but I can't get both. Any advice? Would it be easier to grab the node that contains both coords and just parse out the lat and long?

I'm using

Sub getgeocode(ByVal address As String)
        Dim output As String
        Dim url As String = "http://dev.virtualearth.net/REST/v1/Locations/US/TN/memphis/" & testaddress & "?o=xml&key=AoJgw4TfyKo-D2xbQx-XD8M_O8EJyKu3dZ_TzqKzYwLe6aIQyuW-BciCYra4nJGK"
        Try
        
            Using reader As XmlReader = XmlReader.Create(url)
              
                reader.ReadToFollowing("Latitude")
                Dim lat As String = reader.ReadInnerXml()
                output = "Lat:  " & lat & "<br />"
 
                reader.ReadToFollowing("Longitude")
                Dim lng As String = reader.ReadInnerXml()
                output &= "long:  " & lng
 
            End Using
 
            Response.Write(output)
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Sub

[gep13]

Hello,

In this situation, personally, I would be looking to use LINQ or an XPath Query to loop over the XML to get the value that I was after, not an XmlReader

Can you show a sample of the XML that you are trying to parse, and we can work from there?

Gary

[jculpepper]

I'm passing a address to bing geocoder service and need to extract the lats and longitude:

http://dev.virtualearth.net/REST/v1/...W-BciCYra4nJGK


This is the XML I tried to make it somewhat readable, this comes directly from the url above.

<Response>
  <Copyright>Copyright © 2011 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.</Copyright>
   <BrandLogoUri>http://dev.virtualearth.net/Branding/logo_powered_by.png</BrandLogoUri><StatusCode>200</StatusCode>
<StatusDescription>OK</StatusDescription><AuthenticationResultCode>ValidCredentials</AuthenticationResultCode><TraceId>656a8278ddb042259ce69e0201fcb80a|SN1M001054|02.00.126.3000|SN1MSNVM001331</TraceId><ResourceSets>
<ResourceSet>
<EstimatedTotal>1</EstimatedTotal>
<Resources>
<Location>
<Name>201 Poplar Ave, Memphis, TN 38103-1945</Name>
<Point>
<Latitude>35.149169</Latitude>
<Longitude>-90.047613</Longitude>
</Point>
<BoundingBox>
<SouthLatitude>35.145306282429324</SouthLatitude><WestLongitude>-90.053911548343009</WestLongitude><NorthLatitude>35.153031717570677</NorthLatitude><EastLongitude>-90.041314451656987</EastLongitude>
</BoundingBox>
EntityType>Address</EntityType>
<Address>
<AddressLine>201 Poplar Ave</AddressLine><AdminDistrict>TN</AdminDistrict>
<AdminDistrict2>Shelby Co.</AdminDistrict2>
<CountryRegion>United States</CountryRegion>
<FormattedAddress>201 Poplar Ave, Memphis, TN 38103-1945</FormattedAddress>
<Locality>Memphis</Locality>
<PostalCode>38103-1945</PostalCode></Address><Confidence>High</Confidence>
</Location>
</Resources>
</ResourceSet>
</ResourceSets>
</Response>

[gep13]

Hello,

Ok, to get your current version working, you would have to do something like this:

        Dim output As String
        Dim url As String = "http://dev.virtualearth.net/REST/v1/Locations/US/TN/memphis/201%20Poplar?o=xml&key=AoJgw4TfyKo-D2xbQx-XD8M_O8EJyKu3dZ_TzqKzYwLe6aIQyuW-BciCYra4nJGK"
        Try

            Using reader As XmlReader = XmlReader.Create(url)

                If reader.ReadToFollowing("Latitude") Then
                    Dim lat As String = reader.ReadInnerXml()
                    output = "Lat:  " & lat & "<br />"
                Else
                    Response.Write("Unable to find Latitude")
                End If

                If reader.Read() Then
                    Dim lng As String = reader.Value
                    output &= "long:  " & lng
                Else
                    Response.Write("Unable to find Longitude")
                End If
                

            End Using

            Response.Write(output)
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

Now, why exactly yours wasn't working, I am not sure I "think" it has to do with the fact that ReadToFollowing gets to a place where it can't navigate to the second node, but I can't be sure. However, there is a problem with the above approach in that it assumes that the Longitude node is always the next node in the file, which might not always be the case.

Gary

[jculpepper]

Worked Like a charm! Thanks again.

[gep13]

Not a problem at all.

Bear in mind that the solution provided is not "ideal" and there are likely better ways to achieve it

Gary