Using normalize-string XPath function from SQL XML query ?
- by Ross Watson
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
