Best Practice for SQL2005 CRUD of RSS

Has anybody determined an optimal strategy when storing RSS in the database?
Here are some qualifying reuse considerations once the RSS is saved to the
database:
* the XML needs to be accessible for searching the channel and feed item
descriptions and likely other elements of the feed so query results may be
returned to the page.
* the XML needs to be retrieved and loaded into a Wizard control for editing
and updating nodal values and then resaved to the database.
* the structure of the XML may need to be modified by editors who want
support for various
namespaces for enclosures and so on.
I like this MSDN document [1] but I'm still wondering what the optimal
method to save the XML to the database is and what the optimal method to
retrieve the XML is to optimally support the considerations which qualify
reuse?
Does "your" experience with RSS coincide with the methodologies discussed in
that MSDN document? Hasn't anybody written a best practice document for SQL
Server CRUD of RSS and ASP.NET?
Your comments?
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/
MAP 432'17"N 882'37"W : 432'17"N 882'37"W
[1]
http://msdn.microsoft.com/library/de...xmloptions.asp
Hello clintonG,

> * the XML needs to be accessible for searching the channel and feed
> item descriptions and likely other elements of the feed so query
> results may be returned to the page.
Generally speaking, I'm only in favor of using the XML Data type if your
going to use the data as XML. That means that you are going to query it with
XQuery, you want/need to valid it against an suitable schema and you're okay
spending the disk space to store the primary and potentially secondary XML
indices.
So much depends on how and what you want to query for and how as to how you
want to store it. For example. you could get by with varchar(max) or nvarchar(max)
rather then XML if you use Free-Text searches on the body. Or you could just
the XML type with an Index on it.

> * the XML needs to be retrieved and loaded into a Wizard control for
> editing and updating nodal values and then resaved to the database.
There's nothing on this that help me pick one data type of over the other
here.

> * the structure of the XML may need to be modified by editors who want
> support for various
> namespaces for enclosures and so on.
Then if you pick the XML type, you'll probably have an issue with schema
binding since columnar instances can be bound to a either 0 or 1 XML schema
collection at a time. Text BLOB storage works around this course since it
never binds to any XML schema collection.

> I like this MSDN document [1] but I'm still wondering what the optimal
> method to save the XML to the database is and what the optimal method
> to retrieve the XML is to optimally support the considerations which
> qualify reuse?
Loading an saving would probably be done using the ADO or ADO.NET which,
for all practical purposes, treats XML and the other text BLOBs as the same
thing.

> Does "your" experience with RSS coincide with the methodologies
> discussed in that MSDN document?
Generally speaking, yes.

> Hasn't anybody written a best
> practice document for SQL Server CRUD of RSS and ASP.NET?
No, its probably a little too niche for MS to do.
Cheers,
kt
|||Hello Kent, thanks for replying inline...
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:f2b9912a78538c8b2b35980cce0@.news.microsoft.co m...
> Hello clintonG,
>
> Generally speaking, I'm only in favor of using the XML Data type if your
> going to use the data as XML. That means that you are going to query it
> with XQuery, you want/need to valid it against an suitable schema and
> you're okay spending the disk space to store the primary and potentially
> secondary XML indices.
> So much depends on how and what you want to query for and how as to how
> you want to store it. For example. you could get by with varchar(max) or
> nvarchar(max) rather then XML if you use Free-Text searches on the body.
> Or you could just the XML type with an Index on it.
I'm going to have to use the XML data type -- and -- nvarchar(max). The XML
data type because I will need to return an entire feed expresses as XML when
responding to an AJAX function or a webservice that returns a feed and an
nvarchar(max) because the Primary and three secondary indices of the XML
data type are insufficient to expose an entire feed to search by query.
I'm validating the data on the server when the channel and feed items are
created but I may still need a schema when?

> There's nothing on this that help me pick one data type of over the other
> here.
It looks like I need to get the contents of the nvarchar(max) and populate
an XmlDocument object which is stored in memory to allow each node to be
accessible to each step of a Wizard control that is used to edit any given
node of the feed's XML. I don't know of a more efficient choice that would
allow me to get a node of the XML document on demand and load it into the
step of a Wizard control. Do you?

> Then if you pick the XML type, you'll probably have an issue with schema
> binding since columnar instances can be bound to a either 0 or 1 XML
> schema collection at a time. Text BLOB storage works around this course
> since it never binds to any XML schema collection.
Can I build a broadly scoped schema that declares all possible data types
for all of the known namespaces that I intend to support when extending RSS
and then use whichever data types the context of the feed requires or does
the schema map the entire XML file and then barfs when the XML file does not
contain all of the elements and respective data types that have been
declared in the schema?

> Loading an saving would probably be done using the ADO or ADO.NET which,
> for all practical purposes, treats XML and the other text BLOBs as the
> same thing.
I'll be using the XmlTextWriter and a SQL Provider with a stored procedure
to save the XML as nvarchar(max) until I learn there may be a better way.
I don't mind writing a lot of code. I just hate writing a lot of code I
would not have had to write after discovering I didn't know or gtry to learn
about a more efficient or easier way to write it. Thanks for your replies...
<snip />
<%= Clinton