Fi3ry

I had to create a backup of a table for a website that uses a MySQL 5.x database. Here is the structure used for creating a table from a query:

CREATE TABLE table_name
SELECT * FROM table_to_copy;

I recently decided to use Linq (Language Integrated Query) available in .NET 3.5 SP1 to an XML stream I had to retrieve some information from. So like every other copy and paste expert, I mean programmer I looked to my lady Googles. Here are some basic references:

MSDN .NET Language-Integrated Query for XML Data
A quick intro at blocks4.

This is what the XML stream outputs looks like. [Obviously the names have been changed to protect the innocent.]

<Report>
<ReportItems Count="5">
 <ReportItem Asset="EV-9D9" Longitude="-115.0695" Latitude="36.2657" MessageTime="11/20/2008 9:56 AM" MessageTimeZone="PST" MessageTimeZoneGMTOffset="-480" SpeedUnit="Mph" Speed="14" Direction="North East"  Name="Normal Speed Resumed" />
  <ReportItem Asset="EV-9D9" Longitude="-115.0739" Latitude="36.2629" MessageTime="11/20/2008 9:56 AM" MessageTimeZone="PST" MessageTimeZoneGMTOffset="-480" SpeedUnit="Mph" Speed="25" Direction="North East" Name="Maximum Speed Exceeded" />
  <ReportItem Asset="EV-9D9" Longitude="-115.1464" Latitude="36.1747" MessageTime="11/20/2008 9:41 AM" MessageTimeZone="PST" MessageTimeZoneGMTOffset="-480" Speed="65" Name="Ignition On" />
  <ReportItem Asset="EV-9D9" Longitude="-115.1464" Latitude="36.1748" MessageTime="11/20/2008 1:01 AM" MessageTimeZone="PST" MessageTimeZoneGMTOffset="-480" Speed="n/a" Address="Map Location" Name="Host Synchronization" Version="0" />
  <ReportItem Asset="IG-88" MessageTime="11/19/2008 11:00 PM" MessageTimeZone="PST" MessageTimeZoneGMTOffset="-480" AccumulatedMileage="8" AccumulatedMileageUnit="mi" AccumulatedMileageState="Arizona" Name="Accumulated State Mileage" />
</ReportItems>
</Report>

I had to use Parse method in the XDocument object for the XML because the wrapper from the SOAP object returned a String of XML. Next was to get a Distinct listing of Assets from the stream.

xdocs = XDocument.Parse(report) 'report is the string of xml
Dim query = From AssetReport In xdocs.Descendants("ReportItem") _
                    Select Asset = AssetReport .Attribute("Asset").Value _
                    Distinct

If the XML is coming from a file or from an RSS feed use the Load function.

You’ll notice I used xdocs.Descendants(”ReportItem”) because I was only interested in the information in the ReportItem tags.

Also, for ease of use with the object returned in query I “mapped” Asset to equal the value of the Attribute named Asset in the XML stream.

Now that query contains a distinct listing of Assets, I’ll loop over each one pulling the information I want.

Dim AssetNum As String = String.Empty

For Each item In query

            AssetNum = item.ToString()

            Dim query2 = From DataReport In xdocs.Descendants("ReportItem") _
                    Where (DataReport.Attribute("Name").Value = "Ignition On" _
                    Or DataReport.Attribute("Name").Value = "Ignition Off" _
                    Or DataReport.Attribute("Name").Value = "Breadcrumb" _
                    Or DataReport.Attribute("Name").Value = "Host Synchronization" _
                    Or DataReport.Attribute("Name").Value = "Normal Speed Resumed" _
                    Or DataReport.Attribute("Name").Value = "Maximum Speed Exceeded") _
                    And DataReport.Attribute("Asset").Value = AssetNum _
                    Order By DataReport.Attribute("MessageTime").Value Ascending _
                    Select Asset = DataReport.Attribute("Asset").Value, _
                    Longitude = DataReport.Attribute("Longitude").Value, _
                    Latitude = DataReport.Attribute("Latitude").Value, _
                    MessageTime = DataReport.Attribute("MessageTime").Value, _
                    Speed = DataReport.Attribute("Speed").Value, _
                    Name = DataReport.Attribute("Name").Value, _
                    GMTOffset = DataReport.Attribute("MessageTimeZoneGMTOffset").Value

            For Each item2 In query2
                Try
                    Console.WriteLine("Asset Number: " & item2.Asset & _
                                             ", time: " & item2.MessageTime & _
                                             ", Lat: " & item2.Latitude & _
                                              ", Long: " & item2.Longitude)
                Catch ex As Exception
                    Debug.WriteLine(ex.Message)
                End Try

            Next
            query2 = Nothing
        Next

A variable AssetNum was declared to hold the asset number because you cannot use item.ToString() directly in query2.

For part of the Where clause I was only interested in retrieving the ReportItems with latitudes and longitudes in them. Through some trial and error I found that the ReportItems with Names equaling "Ignition On", "Ignition Off", "Breadcrumb", "Host Synchronization", "Normal Speed Resumed" and "Maximum Speed Exceeded" fit the bill.

The final part of the Where clause is the And where the XML is filtered using the AssetNum variable that contains the current Asset identifier from the distinct list of Assets.

You'll notice the dot notation is used for the Console output. This is because the attribute values are associated in the Linq query. This was not necessary in the distinct query because there was only one Select item returned.