XQuery LIKE statement (starts with)

Question

The value in the field is as follows

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

SQL query

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

And I'm trying to do it like

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

Is indicates an error as

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

Can someone help me, I want to create the feel of a LIKE statement in SQL 2005 XQuery. And I'm new to XQuery.

Answer 1

How about this:

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

Mostly:

• grab the en-US XML element and convert its value to varchar(50)
• then do plain, plain SQL LIKE in varchar(50) column

Answer 2

start-with () / ends-with () can be replaced with combinations of substring () and string-length ():

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

In general, start-with (a, b) is equivalent

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

and ends - with (a, b) is equivalent

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