G
Guest
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?
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?