Using normalize-string XPath function from SQL XML query ?

Question

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

Answer 1

If this is SQL Server 2005/2008, with SQLXML 4.0 this function (normalize) is not supported.

See this MSDN article for more detail.

I would suggest converting the XML to a string and use RTRIM, LTRIM and other string functions of SQL instead.

Answer 2

SQL Server also doesn't include the replace() function which allows regular expressions. You best bet is using contains() which should work for your searches (the nested substring problem won't exist when you are not using wildcards).

select * from @test where data.exist('ns0:thing[contains(@x, "hello")]') != 0