Visual Basic XML Transform – TechEd demo prep

Hello from Orlando! I’ve been at TechEd 2007 this week in very sunny Orlando. As part of my demo prep, I had to create a little xml transform to convert the TechEd schedule that I got off the www.msteched.com website in an Excel XML format into a more readable xml file format. I could have done this all with one transform, but the newer XML file format made for good demo fodder to show off the XML Intellisense features coming in Orcas Beta2.


I thought the code was interesting and fun – a great blend of XML properties, XML Literals, and query – so I’ve posted it below:


Imports System.IO


Imports <xmlns:ss=urn:schemas-microsoft-com:office:spreadsheet>


 


Module Module1


 


 ‘Metadata info


 Enum Column


  Code


  Title


  Description


  Track


  Session


  Level


  Speaker


  Room


  Timeslot


 End Enum


 


 Sub Main()


 


  Dim fromFile = “C:\TechEdScheduleExcelFormat.xml”


  Dim toFile = “C:\MyXMLFormat.xml”


  Dim excelXML = XElement.Load(fromFile)


 


  ‘ Remove first row – metadata info


  excelXML…<ss:Row>.First.Remove()


 


  Dim xml = _


   <?xml version=1.0?>


   <EventSchedule Event=TechEd2007>


    <%= _


     From session In excelXML…<ss:Row> _


     Let info = session…<ss:Data> _


     Select _


      <Event ID=<%= info(Column.Code).Value %>>


       <Title><%= info(Column.Title).Value %></Title>


       <Description><%= info(Column.Description).Value %></Description>


       <Track><%= info(Column.Track).Value %></Track>


       <Session><%= info(Column.Session).Value %></Session>


       <Level><%= info(Column.Level).Value %></Level>


       <Speaker><%= info(Column.Speaker).Value %></Speaker>


       <Room><%= info(Column.Room).Value %></Room>


       <TimeSlot><%= info(Column.Timeslot).Value %></TimeSlot>


      </Event> _


    %>


   </EventSchedule>


 


   xml.Save(toFile)


   Shell(“C:\Program Files\Internet Explorer\iexplore.exe “ & toFile, AppWinStyle.MaximizedFocus)


 


 End Sub


End Module


 


All this, so that in the demo, I could:


1)      Open the resulting file, “MyXMLFormat.xml”, in Visual Studio


2)      Right click on the XML menu item


3)      Select create schema to infer an XSD from that file:


Create Schema


 


4)      Save the schema as an XSD and add it as an existing item in my project:


Save as XSD 


5)      And get an awesome Intellisense experience over the XML:


XML Intellisense


 


Pretty cool, no?