Working wth XML

[Kelly Ford]

I've built an application that relies pretty heavily on XML to enable
flexible creation of data entry forms and storage of the data entered
into those forms. Currently, it stores data as an XML document in an
ntext field. This is great because it allows me a great deal of
flexibility in creating fields. However, I need to implement searching
and sorting of that data and I don't know how to do this in an efficient
manner.

Currently the data is stored in a format similar to this:
<form>
<fieldname>fieldvalue</fieldname>
<listfieldname>
<items>
<item>
<label>displaytext</label>
<value>itemvalue</label>
</item>
</items>
</listfieldname>
</form>

Is there any way to perform searches on data like this, i.e. to search
the "fieldvalue" in <fieldname> or "itemvalue" in <value>?

If there isn't a way to do this, I'm willing to re-structure the
databast architecture, though the solution would still need to allow the
same kind of flexibility that the XML solution does.

Any ideas?

Thanks,
Kelly

[Erland Sommarskog]

Kelly Ford (no**********@knowbetter.com) writes:

I've built an application that relies pretty heavily on XML to enable
flexible creation of data entry forms and storage of the data entered
into those forms. Currently, it stores data as an XML document in an
ntext field. This is great because it allows me a great deal of
flexibility in creating fields. However, I need to implement searching
and sorting of that data and I don't know how to do this in an efficient
manner.

Currently the data is stored in a format similar to this:
<form>
<fieldname>fieldvalue</fieldname>
<listfieldname>
<items>
<item>
<label>displaytext</label>
<value>itemvalue</label>
</item>
</items>
</listfieldname>
</form>

Is there any way to perform searches on data like this, i.e. to search
the "fieldvalue" in <fieldname> or "itemvalue" in <value>?

You have the patindex function which should work with ntext columns. It
does not know about XML through...

Next version of SQL Server, which currently is in beta, will have
extensively better XML support. There is an XML datatype, you can
create XML indexes, and you can use XQuery.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Kelly Ford]

Thanks Erland. That seems to work great. Do you know how well patindex
scales? If the query is searching 1000 or 10000 records, is this still a
viable solution?

Kelly

In article <Xn********************@127.0.0.1>, es****@sommarskog.se
says...

You have the patindex function which should work with ntext columns. It
does not know about XML through...

Next version of SQL Server, which currently is in beta, will have
extensively better XML support. There is an XML datatype, you can
create XML indexes, and you can use XQuery.

[Erland Sommarskog]

Kelly Ford (no**********@knowbetter.com) writes:

Thanks Erland. That seems to work great. Do you know how well patindex
scales? If the query is searching 1000 or 10000 records, is this still a
viable solution?

If you have 10000 rows with ntext column with XML data in them, I would
not expect subsecond response time. If you need good response time, in
SQL2000 you would have to shred the documents with OPENXML and store
the data relationally in tables.

Also, I would advice you check that patindex is able to get past the
4000th character, because I know that there have been bugs in this area.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Rob Wahmann]

I'm really surprised that you're storing the file in a database field...
kinda defeats the purpose, I think. I would read and write to the XML file
directly and bypass the database. It's much quicker for a web server to
read/write to the file system than from a DB. The XML file itself becomes a
database of sorts and it would be searchable through script
(asp,.net,cfml,jsp,php). Sounds like a cool idea nevertheless.

Rob

"Kelly Ford" <no**********@knowbetter.com> wrote in message
news:MP************************@news.west.cox.net. ..

I've built an application that relies pretty heavily on XML to enable
flexible creation of data entry forms and storage of the data entered
into those forms. Currently, it stores data as an XML document in an
ntext field. This is great because it allows me a great deal of
flexibility in creating fields. However, I need to implement searching
and sorting of that data and I don't know how to do this in an efficient
manner.

Currently the data is stored in a format similar to this:
<form>
<fieldname>fieldvalue</fieldname>
<listfieldname>
<items>
<item>
<label>displaytext</label>
<value>itemvalue</label>
</item>
</items>
</listfieldname>
</form>

Is there any way to perform searches on data like this, i.e. to search
the "fieldvalue" in <fieldname> or "itemvalue" in <value>?

If there isn't a way to do this, I'm willing to re-structure the
databast architecture, though the solution would still need to allow the
same kind of flexibility that the XML solution does.

Any ideas?

Thanks,
Kelly