Recommendations for implementing a complex schema in SQL Server using XML Type

My application has a complex schema for the domain entity. It is required use SQL Server 2008. Following are the complexities:

Domain Entity is Hierarchical: The data structure is a tree; it is nested to many levels. Few nodes 开发者_JS百科in the tree are repeatable (multi-valued). For example, the entity can have unlimited addresses (home, billing, shipping, office, etc.)

Domain Entity is Expandable: The schema may expand (not shrink) in future.

Designing such a schema directly as related SQL Server tables is quite challenging. If not designing, quering will surely be so.

I am thinking of using XML type to store the domain entity records. However I have following queries:

• Due to peculiar reporting needs, each field should be query-able (within and across entity records). This applies to even the fields that are added in future to the schema.
• While using XML type, since I lose the structure, what is the best Data Access Layer I can design?
• Can I use Entity Framework effectively in this situation?
• Any best practices recommended?

---

One advice: DO NOT DO IT. Seriously. You are already down a slippery slope - etter learn to use databases.

The "Domain Entity" you define here will be large, which means that querying it will be a challenge.Unlimited addresses means 100.000 plus that you ahve to be prepared to. Anyone stupid enough to ask for the xml document will get a bad surprise, as will the server.

You also loose a lot of tooling left and right - from ORM's to reporting tools. Simply because you abuse wthe XML support the databae has (which is planned to store documents, not act as pseudo database).

Your queries:

Due to peculiar reporting needs, each field should be query-able (within and across entity records). This applies to even the fields that are added in future to the schema.

In the english language, this is not a query, you know. It is also not possible.

While using XML type, since I lose the structure, what is the best Data Access Layer I can design?

Start writing SQL. By hand. Or develop your own. You are way out of what people use XML For, so no predefined tooling support.

Can I use Entity Framework effectively in this situation?

Obviously no.

Any best practices recommended?

Yes, learn using SQL Server properly. This is NOT a good approach.

---

I'm working on an abstraction layer for this: http://rogeralsing.com/2011/02/28/linq-to-sqlxml/

Code is available on https://github.com/rogeralsing/linq-to-sqlxml

You can query and select/project entities from Sql server XML columns. We are using it for evolving entity schemas while keeping old versions intact.

That beeing said, we only use it for special cases and go O/R mapping as a default approach.

---

In all honesty, and whilst I see @TomTom 's point, but it depends whether it is just ONE xml document or not. With 2008, you can setup XML schema's and map them to an XML field.

In contrary to TomTom 's answer, you can query an xml data field like you would do normally. Check the following SO answer for more information: https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008

You can use the entity framework (my knowledge is a bit short on this), by making some sproc's to query your data, then call the sproc from code and cast it to an XDocument. Not the prettiest way of doing it but it should work. Note: there might be another way of doing this, but that's as far as my knowledge of EF goes, perhaps add a tag for EF in the question?

I guess you need to come back to us and state whether you need to query 1 xml document (in which case an relational DB would possibly be better, suggested by @TomTom) or multiple documents (which I would use SQL Server to do the work. Chances are you'll have some way of linking these documents together anyway).

XML indexing tips can be found here

And some more info on XML in SQL 2008 here

Hth,

Stu

---

Did you try SisoDb? If you have any questions about it I would happily answer them. Use the contact form at http://www.sisodb.com or ping me at Twitter.