Re: how would I store the XML

Trees and hierarchies in SQL are fairly complex when it comes to
inserting, updating and maintaining the integrity of the Xml. I
recommend against it, unless your data is purely relational, or you
have the time to invest in learning to do hierarchies correctly.

So, unless you are using SQL Server 2005, I recommend storing the Xml
in an ntext field (but validate it against your schema before storage).
You can always "shred" the xml later (into relational structures).

When you are ready for SQL 2005, use a column of type XML and add your
schema to a collection. This will offer data validation, indexing, and
other benefits not currently available.

Below are a few links on hierarchical data storage, in case you want to
go that route.

Trees in SQL: Nested Sets and Materialized Path
http://www.dbazine.com/oracle/or-articles/tropashko4

SQL Lessons
http://www.dbmsmag.com/9604d06.html

Maintaining Hierarchies
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=8826&Display...


Manipulating Hierarchies with UDFs
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=16123&Displa...


Trees and Hierarchies in SQL
http://www.amazon.com/exec/obidos/tg/detail/-/1558609202/qid=11237899...


p.s.: You might get even better responses by posting to:
microsoft.public.sqlserver.programming

-KJ

.

Relevant Pages