SQLXML XPath data with apostrophe

13 May 2009 2:04 PM

Gil

I'm having an issue trying to retrieve data via SQLXML4 against a
SQLServer2005 database using an XPath in which the data I'm searching for has
an apostrophe.
Code is in C#.
Values without apostrophes return just fine.

For example, the xpath would be something like:
"Users[@UserName='o'brien']"

I've tried
double single quotes: 'o''brien'
': 'o'brien'
concat: concat(\"o\",\"'\",\"brien\") removing the single quotes that
surround the value

all to no avail. The only thing I haven't tried is variable replacement,
@UserName=$var, but not sure how to go around doing that.

The exception I get is SqlXmlException:
MSXML: is an invalid or unsupported XPath
HRESULT = 0x80131501

We are using SqlXmlCommand and SqlXmlAdapter (the code below is not 100%, ie
we provide a schema path and connection string, but is somewhat psudocode)

SqlXmlCommand SQLXMLCmd;

SQLXMLcmd.CommandText = (string)Params[0].Value; // this is the XPath
SQLXMLcmd.CommandType = XPath;

SqlXmlAdapter SQLXMLAd = new SqlXmlAdapter(SQLXMLCmd);
DataSet ds = new DataSet();
SQLXMLAdd.Fill(ds); // this is where the exception is thrown

Any help on this would be greatly appriciated.

Thanks

13 May 2009 3:39 PM

Martin Honnen

Gil wrote:

> Code is in C#.
> Values without apostrophes return just fine.
>
> For example, the xpath would be something like:
> "Users[@UserName='o'brien']"

If that is a C# string literal then
"Users[@UserName = \"o'brien\"]"
should do. If you need both single and double quotes inside the XPath
string literal then you need concat e.g.
"Users[@UserName = concat('He said: \"o', \"'brien\")]"

--

Martin Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/

13 May 2009 6:03 PM

Gil

Thanks. Changing the surrounding quotes to double-quotes did the trick. Of
course if the name has a double-quote the same thing will happen, but it's
less likely.

I've tried the concat method but that doesn't work for me.
I even tried the concat with a username that doesn't have an apostrophe and
it does not work:

string username = "concat(\"b\",\"i\",\"l\",\"l\")"; (tried with single
quotes too)
Xpath = "Users[@UserName=" + username + "]";

Show quoteHide quote

"Martin Honnen" wrote:

> Gil wrote:
>
> > Code is in C#.
> > Values without apostrophes return just fine.
> >
> > For example, the xpath would be something like:
> > "Users[@UserName='o'brien']"
>
> If that is a C# string literal then
>    "Users[@UserName = \"o'brien\"]"
> should do. If you need both single and double quotes inside the XPath
> string literal then you need concat e.g.
>    "Users[@UserName = concat('He said: \"o', \"'brien\")]"
>
>
> --
>
>     Martin Honnen --- MVP XML
>     http://msmvps.com/blogs/martin_honnen/
>