title: How to add an XML CDATA section using SQL xmlserialize function?
body: <p>For a call to a third-party API, I need to prepare some XML content to be used in the HTTP body content.</p>
  
  <p>To do this, XML is generated as a result of an xmlserialize SQL function within an RPG ILE program.
  Everything works fine except when it comes to managing fields with &amp; symbol inside: in this case the third-party API rejects the request for invalid XML content, even if SQL translates <code>&amp;</code> into <code>&amp;amp;</code> by itself.</p>
  
  <p>The SQL statement is something like this:</p>
  
  <pre><code>exec sql
  select xmlserialize(                                                 
  xmldocument(                                                         
    xmlelement(                                                        
     name "Info",                                                      
     xmlconcat(                                                        
      xmlelement(name "SedeGls"                      , :xmlGlsCodSede),
      xmlelement(name "CodiceClienteGls"             , :xmlGlsCodCli ),
      xmlelement(name "PasswordClienteGls"           , :xmlGlsPassCli),
      xmlagg(                                     
       xmlelement(                                                     
        name "Parcel",                                                 
        xmlconcat(                                                     
         xmlelement(name "CodiceContrattoGls"  , :xmlGlsCodCon       ),
         xmlelement(name "RagioneSociale"      , trim(:bor_ORAGDE)  ), 
         ...other elements here...
      )                                     
     )                                 
    )                                  
   ) as clob(1M)                       
  )                                    
  into :xmlData                        
  from TAB_COLLI coc;
  </code></pre>
  
  <p>Now, database field bor_ORAGDE may contain invalid XML chars like <code>&amp;</code>, but I wouldn't like to substitute these characters with others inside the database. I would rather use a CDATA section to hold this database values, in order for the result to be like this:</p>
  
  <pre><code>&lt;RagioneSociale&gt;&lt;![CDATA[my personal content &amp; content]]&gt;&lt;/RagioneSociale&gt;
  </code></pre>
  
  <p>Despite the many efforts and tries, I haven't found any solution yet.
  Simply concatenate <code>&lt;![CDATA</code> and <code>trim(:bor_ORAGDE)</code> and <code>]]&gt;</code> doesn't work because the beginning &lt; and ending > symbols are translated into their corresponding entities <code>&amp;lt;</code> and <code>&amp;gt;</code> , so disregarding the section.</p>
  
  <p>Also, adding a ccdid(1208) to the result variable, after 'as clob(1M)' doesn't work, nor using xmltext to wrap the content.</p>
  
  <p>I can't believe IBM missed such a possibility, so I assume I haven't found the answer in the redbooks yet or I am missing something here.</p>
  
  <p>Does this ring any bell?</p>
  
  <p>Thank you</p>