title: Build an XML with XMLELEMENT - ORACLE SQL 11g query
body: <p>I'm trying to create the following XML using a SQL query (Oracle):</p>
  
  <pre><code> &lt;Changes&gt;
     &lt;Description&gt;Some static test&lt;/Description&gt;
     &lt;Notes&gt;Some static test&lt;/Notes&gt;
  
     &lt;UserChange&gt;
        &lt;Operation&gt;Static Text&lt;/Operation&gt;
        &lt;User&gt;VALUE from Table - record #1&lt;/User&gt;
        &lt;BusinessSource&gt;VALUE from Table #1&lt;/BusinessSource&gt;
        &lt;ApplicationRole&gt;VALUE from Table #1&lt;/ApplicationRole&gt;
     &lt;/UserChange&gt;
      &lt;UserChange&gt;
        &lt;Operation&gt;Static Text&lt;/Operation&gt;
        &lt;User&gt;VALUE from Table - record #2&lt;/User&gt;
        &lt;BusinessSource&gt;VALUE from Table #2&lt;/BusinessSource&gt;
        &lt;ApplicationRole&gt;VALUE from Table #2&lt;/ApplicationRole&gt;
     &lt;/UserChange&gt;
      &lt;UserChange&gt;
        &lt;Operation&gt;Static Text&lt;/Operation&gt;
        &lt;User&gt;VALUE from Table - record #3&lt;/User&gt;
        &lt;BusinessSource&gt;VALUE from Table #3&lt;/BusinessSource&gt;
        &lt;ApplicationRole&gt;VALUE from Table #3&lt;/ApplicationRole&gt;
     &lt;/UserChange&gt;   
  &lt;/Changes&gt;
  </code></pre>
  
  <p>The table I'm using looks like this:</p>
  
  <pre><code>ID    USER     SOURCE   ROLE
  1     test1    src1     role1
  2     test1    src1     role1
  3     test1    src1     role2
  4     user2    src      role
  5     user3    src      role
  6     user1    src      role
  </code></pre>
  
  <p>I want to write a query that will create a dynamic XML based on the values in the table.
  For example:
  The query should only take the values where user='test1' and the output will be the following XML:</p>
  
  <pre><code>&lt;Changes&gt;
     &lt;Description&gt;Some static test&lt;/Description&gt;
     &lt;Notes&gt;Some static test&lt;/Notes&gt;
  
     &lt;UserChange&gt;
        &lt;Operation&gt;Static Text&lt;/Operation&gt;
        &lt;User&gt;user1&lt;/User&gt;
        &lt;BusinessSource&gt;src1&lt;/BusinessSource&gt;
        &lt;ApplicationRole&gt;role1&lt;/ApplicationRole&gt;
     &lt;/UserChange&gt;
     &lt;UserChange&gt;
        &lt;Operation&gt;Static Text&lt;/Operation&gt;
        &lt;User&gt;user1&lt;/User&gt;
        &lt;BusinessSource&gt;src1&lt;/BusinessSource&gt;
        &lt;ApplicationRole&gt;role1&lt;/ApplicationRole&gt;
     &lt;/UserChange&gt;
     &lt;UserChange&gt;
        &lt;Operation&gt;Static Text&lt;/Operation&gt;
        &lt;User&gt;user1&lt;/User&gt;
        &lt;BusinessSource&gt;src1&lt;/BusinessSource&gt;
        &lt;ApplicationRole&gt;role2&lt;/ApplicationRole&gt;
     &lt;/UserChange&gt;   
  &lt;/Changes&gt;
  </code></pre>
  
  <p>I've started to write the query:</p>
  
  <pre><code>SELECT XMLElement("Changes", 
                XMLElement("Description", 'sometext'),
                XMLElement("Notes", 'sometext'),
                XMLElement("FulfillmentDate", 'Some Date'),
  
                    XMLElement("UserChange",
                    XMLElement("Operation", 'sometext'),
                    XMLElement("User", 'sometext'),
                    XMLElement("BusinessSource", 'sometext'),
                    XMLElement("ApplicationRole", 'sometext')                      
  
                    )).GETSTRINGVAL() RESULTs
                 FROM DUAL;
  </code></pre>
  
  <p>I need to iterate on the other values and make them part of the complete XML.</p>
  
  <p>Appreciate your help.</p>
  
  <p>Thanks</p>