Posted by David Browne on 04/26/06 22:32

"John" <johnxhc@yahoo.com> wrote in message
news:1146070397.593189.298920@u72g2000cwu.googlegroups.com...
>
> 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?
>


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

• Next in forum: Re: Need help with SQL satement - newbie
• Prev in forum: Re: No distinct in a select into stement ?
• Thread view: Re: SQLServer 2005 XML DataType is (almost) useless!!! (Please correct me if I am wrong)

[Reply to this message]