Example: Loading RSS feeds into an SQL Server 2005 XML column Redux
Kirk and I are having a lot of fun with this RSS issue. Here's an updated version of my previous example that copes with Unicode BOMs and pre-validates the fetched RSS before it attempts to store it. It demonstrates that typed XML really does validate everything...
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Imports System.IO
Imports System.Text
Imports System.Net
Imports System.Xml
Imports System.Xml.Schema
Module Module1
Private Sub ValidationEvent(ByVal sender As Object, _
ByVal e As ValidationEventArgs)
Throw New ApplicationException(e.Message)
End Sub
Private Function ValidateThenStoreFeed(ByRef URL As String) As Boolean
Dim client As New Net.WebClient()
Dim buffer As String
Dim schema As New XmlSchemaSet()
Dim xrs As New XmlReaderSettings
' Where are we at?
Console.WriteLine("Processing: {0}", URL)
' Load a schema to validate the feed
schema.Add(Nothing, "http://www.thearchitect.co.uk/schemas/rss-2_0.xsd")
xrs.Schemas.Add(schema)
xrs.XsdValidate = True
AddHandler xrs.ValidationEventHandler, AddressOf ValidationEvent
' Get the raw feed
buffer = client.DownloadString(URL)
client.Dispose()
' Parse off any excess BOM and save
Dim startsAt As Integer
startsAt = buffer.IndexOf("<?")
If startsAt > 0 Then
buffer = buffer.Remove(0, startsAt)
End If
Dim fileName As String = "c:\" & Guid.NewGuid().ToString & ".xml"
File.WriteAll(fileName, buffer)
' Create a validating reader on the memory buffer
' need to read that from a file buffer
Dim buffer2 As New FileStream(fileName, FileMode.Open)
' Read that feed using a validating reader
Dim reader As New XmlTextReader(buffer2, XmlNodeType.Document, Nothing)
Dim vreader As XmlReader
vreader = XmlReader.Create(reader, xrs)
' Move to the first content node
vreader.MoveToContent()
' Save the validated result to SQL
Dim conn As New SqlConnection("Data Source=localhost;Initial Catalog=scratch;Integrated Security=True")
Dim cmd As New SqlCommand("insert into dbo.rssFeeds(source,doc) values (@source,@xml)", conn)
Dim parm As New SqlParameter("@xml", SqlDbType.Xml, buffer.Length, ParameterDirection.Input)
parm.Value = vreader.ReadOuterXml
cmd.Parameters.Add(parm)
parm = New SqlParameter("@source", VarChar, URL.Length, ParameterDirection.Input)
parm.Value = URL
cmd.Parameters.Add(parm)
Try
Using conn
conn.Open()
cmd.ExecuteNonQuery()
End Using
Catch ex As Exception
Throw ex
End Try
' Clean up our mess.
buffer2.Close()
File.Delete(fileName)
End Function
Private Function StoreFeed(ByRef URL As String) As Boolean
Dim client As New Net.WebClient()
Dim buffer As String
buffer = client.DownloadString(URL)
Dim reader As New XmlTextReader(buffer, XmlNodeType.Document, Nothing)
client.Dispose()
reader.MoveToContent()
Dim conn As New SqlConnection("Data Source=localhost;Initial Catalog=scratch;Integrated Security=True")
Dim cmd As New SqlCommand("insert into dbo.rssFeeds(source,doc) values (@source,@xml)", conn)
Dim parm As New SqlParameter("@xml", SqlDbType.Xml, buffer.Length, ParameterDirection.Input)
parm.Value = reader.ReadOuterXml
cmd.Parameters.Add(parm)
parm = New SqlParameter("@source", VarChar, URL.Length, ParameterDirection.Input)
parm.Value = URL
cmd.Parameters.Add(parm)
Try
Using conn
conn.Open()
cmd.ExecuteNonQuery()
End Using
Catch ex As Exception
Throw ex
End Try
End Function
Sub Main()
ValidateThenStoreFeed("http://sqljunkies.com/WebLog/ktegels/Rss.aspx")
ValidateThenStoreFeed("http://msdn.microsoft.com/rss.xml")
' We know this would fail...
' ValidateThenStoreFeed("http://primates.ximian.com/~miguel/miguel.rss2")
' This should fail on the insert.
StoreFeed("http://primates.ximian.com/~miguel/miguel.rss2")
Console.WriteLine("Done")
Console.ReadLine()
End Sub
End Module