I know this question has been asked before but I'm still not clear
about the right way to go. Here is the scenario:

*SQL Server 2005
*We need to store descriptive information about files that are
uploaded for customers.
*One or more files can be uploaded for a given customer. Each file is
tagged with an information type (i.e. biographical info, medical
records, identification, etc.)
*For each information type, we have a questionnaire that a customer
service person must fill out. The questionnaires are moderately
complex, multilevel (some questions are "children" of others). We
need to be able to generate these questionnaires from data in the
database and also store the responses. We need to be able to query
the responses across customers.


I could design a relational structure to house lists of questions for
each information type, possible answers to each question and the
actual answers provided. However, the project manager wants to use
XML because he feels this is more "extensible". In essence, both
questionnaires and responses would be stored as XML strings.

If I end up having to query several hundred thousand records, each
with an XML string to represent the response data, can I really hope
to get good performance, even with an XML index?

Bill E.
Hollywood, FL

Bill E. (billmiami2@netscape.net) writes:
> I know this question has been asked before but I'm still not clear
> about the right way to go. Here is the scenario:
>
> *SQL Server 2005
> *We need to store descriptive information about files that are
> uploaded for customers.
> *One or more files can be uploaded for a given customer. Each file is
> tagged with an information type (i.e. biographical info, medical
> records, identification, etc.)
> *For each information type, we have a questionnaire that a customer
> service person must fill out. The questionnaires are moderately
> complex, multilevel (some questions are "children" of others). We
> need to be able to generate these questionnaires from data in the
> database and also store the responses. We need to be able to query
> the responses across customers.
>
>
> I could design a relational structure to house lists of questions for
> each information type, possible answers to each question and the
> actual answers provided. However, the project manager wants to use
> XML because he feels this is more "extensible". In essence, both
> questionnaires and responses would be stored as XML strings.
>
> If I end up having to query several hundred thousand records, each
> with an XML string to represent the response data, can I really hope
> to get good performance, even with an XML index?

Since I've never worked with XML indexes, I cannot say for sure. But
I would not really expect so.

Then again, from the description you give, I get the impression that
the data is quite unwieldy anyway, and you will need some sort of
flexible structure anyway. And that is usually a performance challenge,
no matter how you do it.

If you have the time, maybe you can do some mock-ups with an XML solution
and some real relational solution, and perform some benchmarks. That may
be enough to convince your project manager to change his mind. Or to
convince you...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Bill E." <billmiami2@netscape.net> wrote in message
news:190d4799-75fb-4c35-8383-0180086f3008@k13g2000hse.googlegroups.com...
>I know this question has been asked before but I'm still not clear
> about the right way to go. Here is the scenario:
>
> *SQL Server 2005
> *We need to store descriptive information about files that are
> uploaded for customers.
> *One or more files can be uploaded for a given customer. Each file is
> tagged with an information type (i.e. biographical info, medical
> records, identification, etc.)
> *For each information type, we have a questionnaire that a customer
> service person must fill out. The questionnaires are moderately
> complex, multilevel (some questions are "children" of others). We
> need to be able to generate these questionnaires from data in the
> database and also store the responses. We need to be able to query
> the responses across customers.
>
>
> I could design a relational structure to house lists of questions for
> each information type, possible answers to each question and the
> actual answers provided. However, the project manager wants to use
> XML because he feels this is more "extensible". In essence, both
> questionnaires and responses would be stored as XML strings.
>
> If I end up having to query several hundred thousand records, each
> with an XML string to represent the response data, can I really hope
> to get good performance, even with an XML index?
>

Performance will depend on what the queries are. But those queries are sure
to be far more complex to write. After all, hierarchical models have been
deprecated for many years precisely because their navigational structures
make data harder to analyse and process. XML is ful for data interchange
but not very effective for data management - certainly not more
"extensible".

--
David Portas

Thank you Dave and Erland.

I've put together a relational solution that is actually very simple.
It contains just a few tables, including one with a self-referencing
foreign key. Using a CTE recursive query and a little ADO.NET, I'm
able to generate an XML document that looks as if the whole thing was
done with XML. I suppose I could skip the ADO.NET and generate the
XML with SQL Server but it doesn't really matter. In any case, it
seems that I can have my relational cake and let my manager eat the
XML too. We'll see how it goes.

Bill