« Meeting the Slug | Home | Poor Internet Applications »
Create XML from SQL Server for Tiltviewer
By chris dalby | June 25, 2008
Last night I cobbled together a script so I could output data from a SQL Server 2005 database into an XML file. The reason I did this is because Tiltviewer uses an XML file formatted in a particular way to create an amazing visualisation. Check out an example of a chinposin flickr visualisation using tiltviewer. Very cool, I think you will agree. Especially considering it took me 2 minutes!
So, if .NET is your thing and you would like to export data from SQL Server to the correctly formatted XML file for tiltviewer, here is the script. This script simply runs whenever the page is loaded and creates the XML file in the file location specified. The data is output using a stored procedure. You can then follow tiltviewer instructions to visualise your new data from the xml file.
By the way, this is not a tutorial, I am simply sharing the code. Leave a comment if you have a question. I imagine that I will work this out for php also in the next few days.
You could also add caching to the xml script and automate the way the file is generated. It’s not a million miles from that. So if anyone works that out, it would be appreciated if you link to that here. otherwise, you are free to use as you see fit 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim filename As String = “G:\websites\htdocs\xml\gallery.xml”
Dim mywriter As System.Xml.XmlTextWriter
‘declare the db connection stuff
‘ Create Instance of Connection and Command Object
Dim connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings(”ConnectionString”))
Dim command As SqlCommand = New SqlCommand(”GetProductsInCategory”, connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue(”@CategoryID”, “138″)
‘ extract details
connection.Open()
Dim customerReader As SqlDataReader = command.ExecuteReader()
‘declare the xml stuff
Dim writer As XmlTextWriter = Nothing
writer = New System.Xml.XmlTextWriter(filename, Nothing)
‘Use indenting for readability.
writer.Formatting = Formatting.Indented
‘Write the XML delcaration.
writer.WriteStartDocument()
‘Write a root element.
writer.WriteStartElement(”tiltviewergallery”)
writer.WriteStartElement(”photos”)
‘loop thru the database and create the xml nodes
While customerReader.Read
writer.WriteStartElement(”ph
‘ Loop
writer.WriteAttributeString(”imageurl”, “../ProductImages/” + customerReader(”ImagePath”))
‘Write the title.
writer.WriteElementString(”title”, customerReader(”Name”))
writer.WriteElementString(”Description”, customerReader(”Code”))
‘Write the close tag for the root element.
writer.WriteEndElement()
End While
‘close db
connection.Close()
writer.WriteEndDocument()
‘Write the XML to file and close the writer.
writer.Flush()
writer.Close()
‘Load the file into an XmlDocument to ensure well formed XML.
Dim doc As New XmlDocument()
‘Preserve white space for readability.
doc.PreserveWhitespace = True
End Sub
Here is the Stored Procedure that I used. You can simply or change accordingly:
CREATE PROCEDURE [dbo].[GetProducts]
AS
SELECT Product.ProductID,Product.[Name], Product.Price, Product.Code, Product.ImagePath, Product.CardSize, Product.WebDisplay
FROM Product
WHERE Product.WebDisplay = 1
ORDER BY Product.ProductID Desc
RETURN
GO
Topics: Tech Watch |
July 9th, 2008 at 10:53 pm
[...] so this is Part 2 of what has become a series. In my previous post, I gave you code that will create the XML file from SQL server data using VB.NET needed to use in [...]