Hi,

is it possible to run an SQL query, with an XPath "where" clause, and to trim trailing spaces before the comparison ?

I have an SQL XML column, in which I have XML nodes with attributes which contain trailing spaces. I would like to find a given record, which has a specified attribute value - without the trailing spaces.

When I try, I get...

"There is no function '{http://www.w3.org/2004/07/xpath-functions}:normalize-space()'"

I have tried the following (query 1 works, query 2 doesn't). This is on SQL 2005.

declare @test table (data xml)
insert into @test values ('<thing xmlns="http://my.org.uk/Things" x="hello " />')

-- query 1
;with xmlnamespaces ('http://my.org.uk/Things' as ns0)
select * from @test where data.exist('ns0:thing[@x="hello "]') != 0

-- query 2
;with xmlnamespaces ('http://my.org.uk/Things' as ns0)
select * from @test where data.exist('ns0:thing[normalize-space(@x)="hello "]') != 0

Thanks for any help,

Ross