XML Exception when compare with a number in SELECT with CASE statement

I used CASE statement in SELECT function as following:

SELECT CASE WHEN a < 0 THEN 0
            ESLE a
            END AS "Number 1"
FROM <TABLE_NAME>

This function is in an XML file of a C# website.

When I open that site, it's display an XMLException:

The '0' character, hexadecimal value 0x30, cannot begin with a name. Line ..., position ...

I had searched out and fix as:

SELECT CASE WHEN XMLExists('[a < 0]') THEN 0
            ESLE a
       END AS "Number 1"
FROM <TABLE_NAME>

but still got the Exception. How could I fix this? Thanks for all help!

Jon Skeet
people
quotationmark

If your XML file literally contains WHEN a < 0 then I'm not surprised you're getting an exception - that's not valid XML. It's got nothing to do with the SQL side of things as your attempted fix suggest. You should be escaping the < as &lt;

SELECT CASE WHEN a &lt; 0 THEN 0

In an XML file, that's how you represent text of SELECT CASE WHEN a < 0 THEN 0.

Better yet, don't hand-edit the XML at all. If the file had been created with an XML API, it would have done this escaping for you automatically.

people

See more on this question at Stackoverflow