1 Reply Latest reply: Jan 16, 2013 12:09 PM by AlexAnd RSS

    How to get rid of an undesired extra parent node in my xml output

    985031
      when I write the query bellow, I get in my xml an XXXX parent node surrounding my <debits> node, i-e i get something like this:
      <file>
      <XXXX>
      <debits>
      ....
      </debits>
      </XXXX>
      </file>

      However I don t want the tag <XXXX> surrounding <debits> node, I just want something like the following :
      <file>
      <debits>
      ....
      </debits>
      </file>

      So here is my query that generates the undesired extra <XXXX> </XXXX>:

      SELECT
      XMLElement("file",
      XMLForest( -- File details

      FILETABLE.ID_FILE "fileNumber",
      FILETABLE.ID_PRET_CLT "loanID",
      -- Debits are pulled from both myTable1 and myTable2 tables
      -- First: fetching debits from myTable1 table

      (Select XMLELEMENT("debits",
      (SELECT
      XMLAGG(
      XMLELEMENT("debit",
      XMLELEMENT("referenceNumber", NUM_REFERENCE)
      )
      )
      FROM myTable1
      Where ID_FILE = FILETABLE.ID_FILE
      ),

      -- Second: fetching debits from myTable2 table
      (SELECT
      XMLAGG(
      XMLELEMENT("debit",
      XMLELEMENT("referenceNumber", NUM_REFERENCE)
      )
      )
      FROM myTable2
      Where ID_FILE = FILETABLE.ID_FILE
      )
      )
      from dual
      ) as XXXX
      )
      ) xml
      FROM
      FILETABLE

      But when I remove "as XXXX " from the query, I get the following error:
      parameter 8 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.

      I get that error in the line including -- as XXXX after I comment the as XXXX to become: -- as XXXX

      so how do i re-write my query to get rid of:
      <XXXX></XXXX> tag ;)

      Thanks a lot