title: Creating XML RSS from Oracle
body: <p>What I'm trying to do is create a stored procedure that will generate an xml that looks like the following.</p>
  
  <pre><code>    &lt;?xml version="1.0" encoding="utf-8"?&gt;
      &lt;rss xmlns:georss="http://www.georss.org/georss" version="2.0"&gt;
      &lt;rowset&gt;
      &lt;row&gt;
        &lt;title&gt;myTitle&lt;/title&gt;
        &lt;description&gt;myDescription&lt;/description&gt;
        &lt;georss:point&gt;myLat myLon&lt;/georss:point&gt;
       &lt;/row&gt;
       ...
      &lt;/rowset&gt;
      &lt;/rss&gt;
  </code></pre>
  
  <p>Which I have the following to generate</p>
  
  <pre><code> SELECT DBMS_XMLGEN.getXML(q'!
   SELECT trim(sitedesc)  AS "title", 
      '&lt;a href="../scripts/fmiinfo.asp?file_code=_BLDGLIST&amp;sitecode='||trim(sitecode)||'&amp;forceDB="&gt;'||trim(siteabbrev)||'&lt;/a&gt;'   AS "description", 
      trim(latitude)|| ' ' ||trim(longitude) AS "georss:point"
     FROM LS
     WHERE sold &lt;&gt; 1
      !') FROM dual;
  </code></pre>
  
  <p>Output looks like this</p>
  
  <pre><code>   &lt;ROW&gt;
     &lt;title&gt;Tipton Facilities&lt;/title&gt;
     &lt;description&gt;&amp;lt;a href=&amp;quot;../scripts/fmiinfo.asp?file_code=_BLDGLISTnull=USINTI01null=&amp;quot;&amp;gt;TIP1&amp;lt;/a&amp;gt;&lt;/description&gt;
     &lt;georss:point&gt;40.286008 -86.058054&lt;/georss:point&gt;
     &lt;/ROW&gt;
  </code></pre>
  
  <p>In the 'description' I want to have a link to another page, I understand xml has certain reserved characters like <code>&gt; &lt; &amp; %</code> it looks like the output automatically converts the reserved characters into it's entity reference accordingly.</p>
  
  <p>The problem however is that the program thinks <code>sitecode</code> and <code>forcedb</code> in the url are variables, when they are not, and they get dropped from the results.  </p>
  
  <p>The url should be: <code>&amp;lt;a href="../scripts/fmiinfo.asp?file_code=_BLDGLIST&amp;amp;sitecode=USINTI01&amp;amp;forceDB="&amp;gt;</code></p>
  
  <p>But it's <code>&amp;lt;a href=&amp;quot;../scripts/fmiinfo.asp?file_code=_BLDGLISTnull=USINTI01null=&amp;quot;&amp;gt;</code></p>
  
  <p>Will I need to type out the url with <code>ctr()</code> function to get the desired output?</p>
  
  <p><strong><code>SET SCAN OFF</code> fixes my variable question.<br>
  Updating sample code per below comments.</strong></p>
  
  <pre><code>             SELECT XMLELEMENT("rss", XMLATTRIBUTES('http://www.georss.org/georss' AS "xmlns:georss"),
                  XMLELEMENT("channel",
                               XMLAGG(XMLELEMENT("item",
                                                 XMLFOREST(title AS "title",
                                                            description AS "description",
                                                            point AS "georss:point"))))).EXTRACT('/*')
               FROM (SELECT trim(longitude) || ' ' || trim(latitude) AS point, 
        '../scripts/fmiinfo.asp?file_code=_BLDGLIST&amp;sitecode='||trim(sitecode)||'&amp;forceDB="&gt;'||trim(siteabbrev)||'&lt;/a&gt;' AS description, 
        trim(sitedesc) AS title
        FROM ls 
        WHERE sold &lt;&gt;1
      );
  </code></pre>