2 Replies Latest reply: Aug 23, 2013 5:38 AM by Saxena RSS

    XMLFOREST unable to generate Parent Tag.

    Saxena

      I AM TRYING TO GENERATE AN XML REPORT FROM RELATIONAL TABLE:

       

      Why use XMLFOREST ? :

      There is a "Transfer" BLOCK in the XML Report which I want to omit from the report if all the fields inside the block are NULL.

       

      The problem is with the XMLFOREST BLOCK in the SQL code which is not returning the TAG <Transfer> in the output.

       

      WHAT I AM DOING IS :

      SELECT

              XMLELEMENT("Accounts",

                  XMLELEMENT("Account",

                      XMLATTRIBUTES(

                    rownum AS "recordId",

                      TO_DATE('20130520','YYYYMMDD') AS "dateCorrected",

                      123456 AS "previousBatchId",

                      56789 AS "previousRecordId"

                      ),

                      XMLFOREST(

                           SIG_ROLE                                                AS "SignatoryRole",

                          TO_CHAR(TRANSFER_DATE,'YYYY-MM-DD') AS "TransferDate",

                          NVL(REASON,0)                                         AS "Reason"

                      ) AS "Transfer"

                  )

          ) AS CRDTRPT

          FROM ANY_TABLE a

      ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

      WHICH GIVES ME : I am not getting the <Transfer> TAGS here.

       

      - <Accounts>

       

       

      - <Account recordId="1" dateCorrected="2013-05-20" previousBatchId="123456" previousRecordId="56789">

       

       

      <SignatoryRole>c</SignatoryRole>

       

       

      <TransferDate>1979-10-13</TransferDate>

       

       

      <Reason>U</Reason>

       

      </Account>

      </Accounts>


      +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


           WHAT I  WANT IS :


       

      - <Accounts>

       

       

      - <Account recordId="1" dateCorrected="2013-05-20" previousBatchId="123456" previousRecordId="56789">

       

       

      <Transfer>
        <SignatoryRole>c</SignatoryRole>

       

       

      <TransferDate>1979-10-13</TransferDate>

       

       

      <Reason>U</Reason>
          </Transfer>

       

      </Account>

      </Accounts>



      +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

       

      Any promt reply or hint to solve this issue will be greatly appreciated.

      (I think I am doing Something Silly, which is the cause of this.)

        • 1. Re: XMLFOREST unable to generate Parent Tag.
          odie_63

          Use another enclosing XMLForest, otherwise there's no way Oracle could know you want to generate a parent element.

           

          XMLFOREST(

            XMLFOREST(

              SIG_ROLE                            AS "SignatoryRole"

            , TO_CHAR(TRANSFER_DATE,'YYYY-MM-DD') AS "TransferDate"

            , NVL(REASON,0)                       AS "Reason"

            )

            AS "Transfer"

          )

          • 2. Re: XMLFOREST unable to generate Parent Tag.
            Saxena

            Perfect !!! Thanks for the prompt reply !!

             

            - <Account recordId="1" dateCorrected="2013-05-20" previousBatchId="123456" previousRecordId="56789">
            <Transfer>
            <SignatoryRole>c</SignatoryRole>
            <TransferDate>1979-10-13</TransferDate>
            <Reason>U</Reason>

            </Transfer>

            </Account>