The XML Datatype does not support the case-insensitive search makes the
XML DataType (almost) useless, if I stored the xml in the XML column, I
certainly want to search it, if I could not performance
case-insensitive search, why do I need store the data in the XML
column?

I try the following:

1) The fulltext search will not tell you which element has the match

2) Using Nodes cross Apply, I got the following error:
The column 'x' that was returned from the nodes() method cannot be used
directly. It can only be used with one of the four XML datatype methods
exist(), nodes(), query(), and value() or in IS NULL and IS NOT NULL
checks.

Very frustrated!!!

Any Suggestions & Advice?
Thanks in advance.
John

Here's a stab in the dark, store the data in a redundant case
insensitive field that you could search. Maybe nvarchar(xxx)??

"John" <johnxhc (AT) yahoo (DOT) com> wrote


From BOL this AdventureWorks sample does a string comparision of xml data
using the database's collation:


WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription'
AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{
(/pd:ProductDescription/@ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]',
'varchar(20)') = 'front'
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]',
'varchar(20)') = 'small'


David

John (johnxhc (AT) yahoo (DOT) com) writes:
Since XQuery in SQL Server closely follows the standard for XQuery, I
guess that the standard says that XML is case-sensitive.

The error in 2 does not seem to have anything to do with the sensitivity
of case.

It's probably better to post an example of what you want to achieve,
so people can give some useful advice.

Involving microsoft.public.sqlserver.xml may be a good idea too.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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