How do I retrieve a subquery as XML data in a regular recordset

Hey everyone, I'm assuming this has been asked before, but I searched for a bit but couldn't find it; maybe I'm using the wrong terminology?

Let's say I have 4 tables, one called Documents that holds abstract data.  The other 3 are Leases, Divorces, and Sales.  The document Table holds data shared between each of the other three types of documents, such as Title, Author, Pages, etc AS WELL AS the documentType(one of the other 3 tbls).  The Sales table contains fields like SaleDate, SaleAmount, Seller, Purchaser, etc.  The Leases table contains Leasee, Leasor, LeaseTerm, etc.

So any document I add to my system would use the "Documents" table plus its own table.  BTW, this example is in chapter 7 (pg 176) of Robert Vieira's "Professional SQL Server 2005 Programming" from WROX. (Good book, easy to read)

So now I can create 3 views for each of the concrete Document types to retrieve data for each.  The problem comes in when I want to query the documents all at once.  I was thinking of combining the common fields where possible under a general name, and then including the rest as XML.

Has anyone done anything like this already?  I'm using SQL2005.  Any ideas or suggestions are appreciated.

-JimStart Free Trial