Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gogman
Starting Member
4 Posts |
 Posted - 2004-03-09 : 14:10:42
|
| Here's one I cannot figure out:We have a table that stores XML documents in an IMAGE column. The documents can vary in size from 1k to well over 2 megs and are completely free-form.I need to be able to select certain records based on data in the stored XML documents.I have tried full-text indexing the column as text, tried SUBSTRING in the query (8k limit on the size) and text pointers.I am simply looking for a simple string match like 'type="JR" rid="5"' quotes included.So essentialy: SELECT * FROM DocRecipts WHERE DocImage LIKE '%type="JR" rid="5"%'DocImage is an IMAGE datatype column where each row contains an XML document stored in it.Platform SQL2K on Win2K Server.Can anyone help, please?Thanks in advance.-gogman- |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-09 : 14:23:19
|
| You can not search on image columns. From SQL Server Books Online:"Some restrictions that must be observed, such as not being able to directly reference an ntext, text, or image column in a WHERE clause."It is not recommended that you use image columns. It is recommended that if you must deal with images that you store the image on disk and store the path and filename in the database.Tara |
 |
|
|
gogman
Starting Member
4 Posts |
Posted - 2004-03-09 : 14:29:33
|
quote:
Originally posted by tduggan
You can not search on image columns. From SQL Server Books Online:"Some restrictions that must be observed, such as not being able to directly reference an ntext, text, or image column in a WHERE clause."It is not recommended that you use image columns. It is recommended that if you must deal with images that you store the image on disk and store the path and filename in the database.Tara
Oh man, we can't do that. Security reasons.Well, I guess this is the time for an ugly hack.Strangely, the full-text index does find some values (have them indexed as .txt) but not all. I think the quotes and spaces are messing it up.Thanks for the quick reply.-gogman- |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-03-09 : 14:51:40
|
| You might be able to add an addtional column to that table and put some sort of "keywords" in that field that relate to the XML.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
gogman
Starting Member
4 Posts |
Posted - 2004-03-09 : 15:38:05
|
quote:
Originally posted by MichaelP
You might be able to add an addtional column to that table and put some sort of "keywords" in that field that relate to the XML.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Thanks for the reply.We already have that, just gonna have to convert all the XML data records to a TEXT datatype.I dread using TEXT data, but this it is a requirement that ALL data (files, XML, images, ect) reside in a central point.The binary files are just fine in IMAGE columns and work correctly with the full-text index.So, time for the ugly hack :) But, the users will never know and that's what matters.I already whiped out a test app to add a new column that is a TEXT datatype and copy the data in there. The queries work as expected. Now to test the XML transport layer with the new data type.Thanks to everyone for the quick feedback!Mucho mucho appreciated!-gogman- |
|
|
|
|
|
|
|
|
Querying Stored XML