This discussion is archived
7 Replies Latest reply: Jan 16, 2013 10:08 AM by 985031 RSS

Generate xml element from Union of 2 Select queries

985031 Newbie
Currently Being Moderated
Do you know ho I can get the union result of 2 queries and put them in xml result, but I want each query to be in seperate xml element.
I don t want to put 1 single xmlelement and do a From then construct a virtual table uniting the 2 subqueries

I mean I don t want something like the following:

(Select
XMLAGG(
XMLELEMENT("credit",
XMLForest(field1 "detail")
)
)
FROM
(
--SubQuery1
Select field1 from myTable1
union
--SubQuery1
Select field1 from myTable2
)
) "credits"

I want something like here:

XMLELEMENT("credits",
(SELECT
XMLAGG(
XMLELEMENT("credit",
XMLForest(field1 "field1")
)
)
FROM
myTable1
),
(SELECT
XMLAGG(
XMLELEMENT("credit",
XMLForest(field1 "field1")
)
)
FROM
myTable2
)
)

Except the 2nd alternative is not working Sad
I get error message:
"SQL command not properly ended"

Thanks a lot
  • 1. Re: Generate xml element from Union of 2 Select queries
    AlexAnd Guru
    Currently Being Moderated
    >
    I want something like here:

    XMLELEMENT("credits",
    (SELECT
    XMLAGG(
    XMLELEMENT("credit",
    XMLForest(field1 "field1")
    )
    )
    FROM
    myTable1
    ),
    (SELECT
    XMLAGG(
    XMLELEMENT("credit",
    XMLForest(field1 "field1")
    )
    )
    FROM
    myTable2
    )
    )
    >

    try
    select XMLELEMENT("credits",
                      (SELECT XMLAGG(XMLELEMENT("credit",
                                                XMLForest(field1 "field1")))
                         FROM myTable1),
                      (SELECT XMLAGG(XMLELEMENT("credit",
                                                XMLForest(field1 "field1")))
                         FROM myTable2))
      from dual
    it must be works
  • 2. Re: Generate xml element from Union of 2 Select queries
    985031 Newbie
    Currently Being Moderated
    Sweet ;)
  • 3. Re: Generate xml element from Union of 2 Select queries
    985031 Newbie
    Currently Being Moderated
    One more question regarding this pls. when I write the query bellow, I get in my xml an XXXX parent node surrounding my <debits> node, 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.

    in 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
  • 4. Re: Generate xml element from Union of 2 Select queries
    AlexAnd Guru
    Currently Being Moderated
    try
    SELECT XMLElement("file",
                      XMLForest( -- File details
                                
                                FILETABLE.ID_FILE "fileNumber",
                                FILETABLE.ID_PRET_CLT "loanID"), -- new
                                -- 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
  • 5. Re: Generate xml element from Union of 2 Select queries
    985031 Newbie
    Currently Being Moderated
    Hi Alex, so basically the only change you added is this : /*as XXXX)*/ commenting the as XXXX, right ?
    I tried that already, that s why I wrote:
    when I comment "as XXXX" .... I get the error
    parameter 7 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.

    Edited by: 982028 on 16 janv. 2013 10:03
  • 6. Re: Generate xml element from Union of 2 Select queries
    985031 Newbie
    Currently Being Moderated
    oh sorry , i just saw u made other changes, ok i will try that first, sorry for the innatentive response :(
  • 7. Re: Generate xml element from Union of 2 Select queries
    985031 Newbie
    Currently Being Moderated
    Work amazingly, thank for saving my day ;)

Legend

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