title: Getting XML from Five Different Oracle Tables
body: <p>I need to get xml like </p>
  
  <pre><code>&lt;dataset code="123" title="" pubcode="456" minrows="0"&gt;
      &lt;schema code="s1" /&gt; &lt;!-- can be one --&gt;
      &lt;rowset code="rs1" /&gt; &lt;!-- can be one --&gt;
      &lt;sorter&gt;
          &lt;!-- field can be MORE than one --&gt;
          &lt;field name="field1" order="o1"/&gt;
          &lt;field name="field2" order="o2"/&gt;
      &lt;/sorter&gt;
      &lt;!-- filter can be MORE than one --&gt;
      &lt;filter type="filter1" value="val1" /&gt;
      &lt;filter type="filter2" value="val2" /&gt;
  &lt;/dataset&gt;
  </code></pre>
  
  <p>Where each tag corresponds to a a separate table. 
  And each attribute in that tag is a column in the corresponding table
  Have written below sql for same</p>
  
  <pre><code> SELECT  XMLELEMENT(NAME "dataset",
                         XMLAttributes(ds.DataSet_Code AS "code",ds.DataSet_Title as "title",ds.pub_code as "pubcode",ds.Min_Rows as "minrowss"),
                         XMLFOREST(  
                              (SELECT XMLElement("schema", XMLAttributes(fs.schema_code AS "code"))
                              FROM File_Schema fs WHERE fr.dataset_code = ds.dataset_code),
                              (SELECT XMLElement("rowset", XMLAttributes(fr.rowset_code AS "code")) FROM File_RowSet fr
                                WHERE fr.dataset_code = ds.dataset_code),
                              (SELECT XMLELEMENT(NAME "sorter",
                                          XMLAGG(XMLELEMENT(NAME "field",
                                                              XMLATTRIBUTES(fsf.field_name AS "name",fsf.field_order AS "order")
                                                            )
                                                  )
                                          )
                              FROM File_sorter_field fsf WHERE fsf.dataset_code=ds.dataset_code),
                              (SELECT XMLAGG(XMLELEMENT(NAME "filter", XMLATTRIBUTES(type AS "type",value AS "value")))
                                          FROM File_Filter ff where ff.dataset_code=ds.dataset_code)
  
                      ))
  FROM File_Product fp , File_DataSet ds
  WHERE fp.File_Name = ds.File_Name and fp.File_Name = 'abc' and ds.dataset_code ='123' ;
  </code></pre>
  
  <p>for which I get error like below</p>
  
  <pre><code>    ORA-19208: parameter 1 of function XMLFOREST must be aliased 19208. 00000 
    - "parameter %s of function %s must be aliased" *Cause: The indicated parameter 
    of the XML generation function has not been aliased, although it is an expression.
     *Action: Specify an alias for the expression using the AS clause. 
     Error at Line: 19 Column: 5
  </code></pre>
  
  <p>any help appreciated</p>
  
  <p><strong>as per KPater87 modified query to</strong> </p>
  
  <pre><code>SELECT  XMLELEMENT(NAME "dataset",
                         XMLAttributes(ds.DataSet_Code AS "code",ds.DataSet_Title as "title",ds.pub_code as "pubcode",ds.Min_Rows as "minrowss"),
                         XMLConcat(  
                              SELECT XMLElement("schema", XMLAttributes(fs.schema_code AS "code"))
                              FROM File_Schema fs WHERE fs.dataset_code = ds.dataset_code,
                              SELECT XMLElement("rowset", XMLAttributes(fr.rowset_code AS "code")) FROM File_RowSet fr
                                WHERE fr.dataset_code = ds.dataset_code,
                              SELECT XMLELEMENT(NAME "sorter",
                                          XMLAGG(XMLELEMENT(NAME "field",
                                                              XMLATTRIBUTES(fsf.field_name AS "name",fsf.field_order AS "order")
                                                            )
                                                  )
                                          )
                              FROM File_sorter_field fsf WHERE fsf.dataset_code=ds.dataset_code,
                              SELECT XMLAGG(XMLELEMENT(NAME "filter", XMLATTRIBUTES(type AS "type",value AS "value")))
                                          FROM File_Filter ff where ff.dataset_code=ds.dataset_code 
  
                      ))
  FROM File_DataSet ds
  WHERE ds.File_Name = 'abc' and ds.dataset_code ='123' ;
  </code></pre>
  
  <p>still get error </p>
  
  <p>ORA-00936: missing expression
  00936. 00000 -  "missing expression"
  *Cause:<br>
  *Action:
  Error at Line: 4 Column: 28</p>