Recommendations - XML or traditional relational structure

[Joel H]

I'm designing a medical record system and am considering using XML in the
database (native SQL Server XML datatype). I'm looking for advise and
opinions as to whether my rational is good, and to get pros and cons of
different strategies.

The application is an ASP.Net 2.0 application written in VB.Net with VS
2005. The backend is SQL Server 2005. We currently use SQL tables and
stored procedures extensively with no XML. Many of the web "forms" that will
be built to collect information from users have many common attributes that
will be included in a "header" or "master" table. However, each form will
contain different content, which may contain multiple master-detail
relationships etc. I am considering putting this content into an XML field
in the master table rather than creating multiple child tables for each type
of form.

The reason I want to do this is to reduce the number of SQL tables, stored
procedures, data access code, and reduce the possibility of data access bugs,
and reduce the number of changes to the database schema (since changes to the
forms will be frequent and ongoing). We may have hundreds of different forms
that are filled in over time by different users. Every change that is made
to the data in a form needs to be tracked - which user changed each piece of
information, and I need to protect against concurreny conflicts. If the
content of the forms were stored in a single xml field, I could track all
changes with only one additional table and the updates could always be
handled by the same update stored procedure, which could handle change
tracking and concurrency issues. I also need to be able to be able to search
the XML data, ie list all forms containing user defined parameters.

My concern is two-fold. 1) Will SQL search the XML data as fast as
traditional tables? If not, how slow is XML? Do I need the Enterprise
version of SQL Server to index and search XML? 2) Will our programming
development time be reduced? We are currently quite proficient with TSQL,
but we have not learned or used the XML features in SQL Server. Xpath,
Xquery, etc do not seem as natural to me as SQL. I can get used to it if I
need to, but it looks more tedious. What other issues should I consider?

[bruce barker \(sqlwork.com\)]

this is nothing wrong with your approach. It a matter of tradeoffs.

1) the performance will depend on what you scans look like. also will you be
using typed or untyped xml (again another set of tradeoffs). if you index
the xml, an index row is created for every node/value pair in the document,
so storage will be larger. also xml storage requirements is larger than
tables.

2) your group will have to learn xpath querying, as a xpath will be required
to access any node. some of your developers may never get the hang of it,
but should be able to use the copy and paste.
-- bruce (sqlwork.com)


"Joel H" <Jo***@discussi ons.microsoft.c om> wrote in message
news:BC******** *************** ***********@mic rosoft.com...

I'm designing a medical record system and am considering using XML in the
database (native SQL Server XML datatype). I'm looking for advise and
opinions as to whether my rational is good, and to get pros and cons of
different strategies.

The application is an ASP.Net 2.0 application written in VB.Net with VS
2005. The backend is SQL Server 2005. We currently use SQL tables and
stored procedures extensively with no XML. Many of the web "forms" that
will
be built to collect information from users have many common attributes
that
will be included in a "header" or "master" table. However, each form will
contain different content, which may contain multiple master-detail
relationships etc. I am considering putting this content into an XML
field
in the master table rather than creating multiple child tables for each
type
of form.

The reason I want to do this is to reduce the number of SQL tables, stored
procedures, data access code, and reduce the possibility of data access
bugs,
and reduce the number of changes to the database schema (since changes to
the
forms will be frequent and ongoing). We may have hundreds of different
forms
that are filled in over time by different users. Every change that is
made
to the data in a form needs to be tracked - which user changed each piece
of
information, and I need to protect against concurreny conflicts. If the
content of the forms were stored in a single xml field, I could track all
changes with only one additional table and the updates could always be
handled by the same update stored procedure, which could handle change
tracking and concurrency issues. I also need to be able to be able to
search
the XML data, ie list all forms containing user defined parameters.

My concern is two-fold. 1) Will SQL search the XML data as fast as
traditional tables? If not, how slow is XML? Do I need the Enterprise
version of SQL Server to index and search XML? 2) Will our programming
development time be reduced? We are currently quite proficient with TSQL,
but we have not learned or used the XML features in SQL Server. Xpath,
Xquery, etc do not seem as natural to me as SQL. I can get used to it if
I
need to, but it looks more tedious. What other issues should I consider?