GEL Script XOG - Handling Special Characters

[Anon Anon]

One of the data fields in my query occasssionally contains an ampersand and a single quote.

This results set is then used in my GEL Script XOG.

Example #1:
Name = A & R

In my SQL query I currently use to try to present the data in a way that can be used in my XOG script:
SELECT replace(NAME, '&', '&') NAME2
FROM ODF_CA_TABLENAMEX

Resutls returned:
A & R

What is the best way to clean the data and be able to use it for XOG?

Thanks in advance!

[navzjoshi00]

Check these -
http://www.xmlnews.org/docs/xml-basics.html
http://www.freeformatter.com/xml-escape.html
http://www.asciitable.com/


NJ

[Anon Anon]

Thanks NJ!

[Sergiu.Gavrila]

Hi,

have you tried putting your sql in einem CDATA block?

Something like :

<sql:query escapeText="0" var="result"><![CDATA[
SELECT  ... FROM ....
]]></sql:query>

Search the forum after CDATA and GEL and you may find some useful examples for handling special characters in GEL.

Best regards,

Sergiu Gavrila

------------------
Clarity Add-Ons www.itdesign.de/itd-addons/
Real Time Workload Analytics meisterplan.com

[Anon Anon]

Hi Sergiu,

Thanks for your reply! I did try to use the CDATA tag and the escapeText code around my SQL query, but when the process created the XML XOG code it still didn't XOG in the correct data. I would see & in the field.

I thought maybe I could use that util:replace tag in the section of the script where the values from the SQL query are being assigned to variables, but I keep getting this error and it doesn't allow me to save my updates to the script.
BPM-0703: Custom script syntax error at line 67, column 102: The prefix "util" for element "util:replace" is not bound.

I saw a posting where someone replaced the amp; with "" so I thought I'd try that in my SQL query. It looks good when I run the SQL query, but when the process creates the XML XOG script it still added extra amp;amp; to the field. :sad

thanks,
christine

[Eugene Greiff]

Maybe you could replace your query with something like this to remove the & char.

SELECT replace(NAME, CHAR(38), CHAR(38), CHAR(38)+'amp' + CHAR(59) NAME2
FROM ODF_CA_TABLENAMEX

If you have an Oracle backend, I think it is CHR instead of CHAR.

V/r,
Gene

[Anon Anon]

Hi Gene,

Thanks for your reply and suggestion! = )

I did try your suggestion, but I wasn't successful. = ( When I tried to run the SQL statement in my query tool it says "ORA-01722: invalid number" and doesn't run the query....same result when I try to run it through my process. = (

If I do not use any replace function in my SQL query the results for the NAME filed is returned as :
A & R

The above is the value that I get when I write the value of the assigned variable to the <gel:log>.

It XOGs in as A &amp;amp; R

I was wondering....is there a way to clean the data in the same line where I assign the data to a variable before it is used in the GEL XOG? If could somehow strip the amp; from the variable before it is used in the GEL XOG in theory I think it would be ok....?


thanks,
christine

[Eugene Greiff]

When you tried the replace the error you received looks like you might be missing the jelly:util namespace in your <gel:script tag

The prefix "util" for element "util:replace" is not bound.

Gene

[Eugene Greiff]

This worked for me.

Parse.xml contained records which had the &amp;amp;.


<row>
<field column="NAME2">FY13 O&amp;amp;M - BNLER</field>
</row>
<row>
<field column="NAME2">FY12 RDT&amp;amp;E - Pharmacy</field>
</row>

Added this namespace xmlns:util="jelly:util" to the gel:script tag.

<util:loadText file="Parse.xml" var="ToClean" />

<util:replace old=";amp;" new=";" var="Cleaned" value="${ToClean}"/>>


<core:new className="java.io.PrintWriter" var="PrintOne">


<core:arg type="java.lang.String" value="Cleaned.xml" />


<core:arg type="java.lang.String" value="UTF-8" />

</core:new>



<core:invoke method="print" on="${PrintOne}">


<core:arg value="${Cleaned}" />

</core:invoke>



<!-- Close the file -->

<core:invoke method="close" on="${PrintOne}" />

After the replace Cleaned.xml contained:

<row>
<field column="NAME2">FY13 O&amp;M - BNLER</field>
</row>
<row>
<field column="NAME2">FY12 RDT&amp;E - Pharmacy</field>
</row>

[Anon Anon]

Hi Gene,

Thank you very much for all your help! I was missing the xmlns:util="jelly:util" in my custom script. After I added it I the util:replace code worked. :smile:smile

thank you!
christine