I have some SQL code which needs to read a string from SQLServer database containing XML code. The XML code was not converted properly and the contains '<' and '>' I need to convert these to <> \, however WebFocus thinks these are parm values and prompts me for input value.

Is there a way for me to tell webfocus to just pass the SQL thru?

SQL SQLMSS


select *,PreCertXmlcol.query('//out-of-state-medical-services-request-form/patient-information/subscriber-id="866324406M"'),
PreCertXmlcol.query('//out-of-state-medical-services-request-form/patient-information'),
PreCertXmlcol.query('//out-of-state-medical-services-request-form/admission-information/admission-date')
from
(
select ID,convert(xml,replace(replace(convert(nvarchar(max),additional_attributes.query('
for $myxml in //attributes/attribute
where $myxml/name="marshalledXml"
return
$myxml/value/text()

)),'&'+'lt;','<'),'&'+'gt;','>')) as PreCertXmlcol
,additional_attributes
from TestMessageCenter.dbo.mc_message where sub_system='Precertification' and ID=2153
and additional_attributes.exist('//attributes/attribute/name="marshalledXml"')=1
) as t
;

WebFocus Server 7.6.10

This message has been edited. Last edited by: Kerry,

I got it to working by creating -set
-SET & lt = '& lt';
-SET & gt = '& gt';

Can't believe that worked.

The spaces above are just to keep this editor from translating it to <>.

In the future, you can use code tags. Just look for the </> button in the post's toolbar.

Another way to stop WF trying to eval the & is to use the pipe char.

e.g.

&|lt

Not when using sql server syntax for passthru. A pipe is not proper syntax for tsql.

Its good to know that your know tsql, but this is FOCUS interpreting the code, and the pipe will be evaluated by FOCUS, and not tsql, the pipe only stops FOCUS from trying to evalulate the amper variable. Once that part of the FOCUS interpretation is done the pipe will be missing and the string with the & will be passed to tsql.

Perhaps you could try it out to see.

solved