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 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:
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.