I have a SQL query which returns results and places the results in an XML file. the problem is that one of the fields can contain ampersands (&) in the field, so when a bare & is put into an XML file, I get XML errors because they have to be formatted in an XML friendly way. To get around this, I want to insert a REPLACE into my SELECT statement, but I need to somehow escape it so that it prints exactly what I want, and not interpret my replacement pattern. The part of the query I have is this:
select e.guid as OWNER_GUID,
e.id as DEALER_ID,
replace(e.NAME,'&','&'
) as DEALER_NAME,
ol.op_code as OP_CODE,
ol.op_description as OP_DESCRIPTION
from auto_vehicleservice.opcode
_lookup#00
1 ol,
ia.entities e,
In the replace, I'm replacing all occurrences of '&' with the HTML/XML-compatible syntax for &, which is '&'. I save this query within my web-based job and run it, and it runs fine, but I come back to look at the query, and it shows me:
select e.guid as OWNER_GUID,
e.id as DEALER_ID,
replace(e.NAME,'&','&') as DEALER_NAME,
ol.op_code as OP_CODE,
ol.op_description as OP_DESCRIPTION
from auto_vehicleservice.opcode
_lookup#00
1 ol,
ia.entities e,
The '&' has been replaced with '&'. That bothers me, because I'm afraid if that executes, it will not do the replacement, and the XML file that's generated will still come out with bare &'s in it. How do I escape that '&' in my replace, so that it will not interpret that & and change the query around? I want '&' to be printed in the output XML, so that it will not have problems when that outputted XML file is opened. thanks!
Start Free Trial
