7 Replies Latest reply: Jan 16, 2013 12:08 PM by 985031 RSS

    Generate xml element from Union of 2 Select queries

    985031
      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
          >
          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
          • 3. Re: Generate xml element from Union of 2 Select queries
            985031
            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
              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
                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
                  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
                    Work amazingly, thank for saving my day ;)