xQuery LIKE-operator (starts-with)

Question

Value in the field is as follows

<en-US>Parameter23</en-US>
<it-IT>Parameter</it-IT>

SQL query is

select * 
from parametermaster 
where cast(ParameterName as xml).exist('en-US/text()[contains(.,"P")]') = 1

And I am trying to it as

select * 
from parametermaster 
where cast(ParameterName as xml).exist('en-US/text()[starts-with(.,"P")]') = 1

Is is giving error as

Msg 2395, Level 16, State 1, Line 1
XQuery [exist()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:starts-with()'

Can anyone help me please, I want to create LIKE operator feeling in SQL 2005 XQuery. And I am an newbie in XQuery.

Answer 1

starts-with()/ends-with() can be substituted by combinations of substring() and string-length() functions:

select * 
from parametermaster 
where cast(ParameterName as xml).exist('en-US/text()[substring(., 1, string-length("P")) = "P"]') = 1

In general starts-with(a, b) is equivalent to

substring(a, 1, string-length(b)) = b

and ends-with(a, b) is equivalent to

substring(a, string-length(a) - string-length(b)) = b

Answer 2

How about this:

select * 
from parametermaster 
where cast(ParameterName as xml).value("(en-US)[1]", "varchar(50)") LIKE 'P%'

Basically:

• grab the en-US XML element and convert its value to a varchar(50)
• then do a regular, normal SQL LIKE on that varchar(50) column