This discussion is archived
1 Reply Latest reply: Jan 16, 2013 10:09 AM by AlexAnd RSS

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

985031 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points