Example: Loading RSS feeds into an SQL Server 2005 XML column
There's been a brief discussion on the SQL Server 2005 XML newsgroup regarding RSS and the XML type on SQL, encoding and schema. In keeping with my recent series of code-heavy examples, here's the guts of a primvative little VB.NET console app that sucks feeds into a table.
The basic gist of the problem is that the XML type doesn't look at the content to determine encoding unless the source that being converted from is binary, but if an encoding is given in the prolog other than UTF-16 (in most cases), SQL complains that it can't switch the encoding. That's seldom the case with client side code where we are using SqlXml typed parameters. It seems the trick is to parse off the document prolog so that we're just feed the typed column data and not encoding information.
Continues at http://sqljunkies.com/WebLog/ktegels/articles/5013.aspx