Query XML data in SQL Server using replace in criteria

Question

I have an XML column in my SQL Server 2008 table. What I'm trying to do is for a given parameter to my stored procedure strip out any spaces (using REPLACE) in the param and use this in the WHERE criteria but then using the XQuery exist clause also use the REPLACE method on the xml data:

-- Add the parameters for the stored procedure here
@PostCode varchar(20) = ''
AS
BEGIN
  -- strip out any spaces from the post code param
  SET @PostCode = REPLACE(@PostCode, ' ','')

  SELECT TOP 1 *
  FROM sd_LocalAuthorities
  WHERE PostCodes.exist(N'REPLACE(/PostCodes/PostCode/text(), '' '','''')[. = sql:variable("@PostCode")]') = 1   
END

I'm getting the error at XQuery sd_LocalAuthorities.PostCodes.exist()

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

when running the procedure. Is there any alternatives to REPLACE() I can use to strip out spaces just for this WHERE criteria, I don't want to be modifying the table itself.

Exist function need XQuery expression as parameter, not T-SQL function. Working, but *unefficient* method is to cast PostCodes as varchar, replace double spaces and cast as xml. — EKOlog, Feb 10 '15 at 14:58
Thanks for the pointer I've tried this and like you say it's not very efficient , it's taking 10 seconds plus to run the query — user3783297, Feb 10 '15 at 15:10

Rhys Jones · Accepted Answer · 2015-02-10T15:57:59.697

There is an XQuery function 'replace' but it's not available in TSQL where you want to use it. As an alternative approach you could pull the postcodes out of the XML and do the replace on native values. Something like this;

declare @sd_LocalAuthorities table (id int, postcodes xml)
declare @PostCode varchar(20); set @PostCode = 'BB11BB'

insert @sd_LocalAuthorities values (1, N'<PostCodes><PostCode>AA1 1AA</PostCode></PostCodes>')
insert @sd_LocalAuthorities values (2, N'<PostCodes><PostCode>BB1 1BB</PostCode></PostCodes>')
insert @sd_LocalAuthorities values (3, N'<PostCodes><PostCode>CC1 1CC</PostCode></PostCodes>')

select top 1
    la.*
from 
    @sd_LocalAuthorities la
        cross apply la.postcodes.nodes('/PostCodes/PostCode') as t(c)
where 
    replace(t.c.value('.', 'varchar(20)'), ' ', '') = @PostCode

This approach is more precise than converting the whole XML document/fragment to varchar because it only performs the replace on the postcode values. Depending on your circumstances an XML index may help performance.

Thanks works well, although surprisingly it's only slighltly more efficient than Casting the column to Varchar(Max) doing the replace then casting back to xml. — user3783297, Feb 10 '15 at 15:21

Query XML data in SQL Server using replace in criteria

1 Answers1