3 Replies Latest reply: Jun 28, 2012 2:04 PM by Jason_(A_Non) RSS

    How to create Nested (Multi level ) tag in XML using DBMS_XMLQUERY function

    740041
      Hi,

      I need Following out put in CLOB Column.
      XML format Like :

      <?xml version="1.0" encoding="UTF-8"?>
      <ReceiptHeader>
      <Id>1234556</Id>
      <Type>DD</Type>
      <Receipts>
      <ReceiptDEO>
      <StoreId>11380001</StoreId>
      <EmployeeId>NOLO980</EmployeeId>
      <LineItems>
      <ReceiptLineItem>
      <CartonId>ABC12345</CartonId>
      <ShippedQty>1.0000</ShippedQty>
      <UnitCost>118.500000</UnitCost>
      </ReceiptLineItem>
      </LineItems>
      <ReceiptDate>Wed Jun 20 11:50:01 CEST 2012</ReceiptDate>
      <ReceiptNumber>3127855</ReceiptNumber>
      </ReceiptDEO>
      </Receipts>
      </ReceiptHeader>


      And i'm using dbms_xmlquery.newcontext ( );

      Can you please help to get above output.

      - Thanks,
      Pallavi
        • 1. Re: How to create Nested (Multi level ) tag in XML using DBMS_XMLQUERY function
          odie_63
          Hi,

          I'm afraid you're on the wrong track with DBMS_XMLQUERY.
          It may be possible by involving some object types or XSLT but it'll require some efforts.
          I think SQL/XML functions will serve you better.

          What's the db version? (SELECT * FROM v$version)

          Can you post some sample data from the base table(s)?
          • 2. Re: How to create Nested (Multi level ) tag in XML using DBMS_XMLQUERY function
            740041
            Hi ,
            Thanks for reply .

            Version is "Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production".
            First I tried with DBMS_XMLGEN ,, it doesnt work properly.
            Then tried with XMLAGG and XMLELEMENT but giving me output in 1 row. I need proper XML format.

            Following is some data, For which I had created view of 2 tables.
            ID     STORE_ID     EMPLOYEE_ID     ACTUAL_ARRIVAL_DATE     CARTON_ID     SHIPMENT_ID     QUANTITY_RECEIVED     UNIT_COST     RECEIPT_DOC_ID     RECEIPT_Date
            3772     12340001     PALS001     16-Feb-11     2A632     29     28.000     118.50000     1234     16-Feb-11
            3775     12340001     PALS001     16-Feb-11     1A633     30     120.000     218.50000     7866     16-Feb-11
            3776     12340001     PALS001     16-Feb-11     2A634     30     75.000     345.75000     7876     16-Feb-11
            3773     12340001     PALS001     16-Feb-11     3A632     30     45.000     200.10000     7869     16-Feb-11
            3774     12340001     PALS001     16-Feb-11     3A632     30     10.000     450.45000     7869     16-Feb-11

            Please suggest better way..

            -Thanks,
            Pallavi
            • 3. Re: How to create Nested (Multi level ) tag in XML using DBMS_XMLQUERY function
              Jason_(A_Non)
              I don't see how most of that data in the sample you provided maps to your XML. Please provide an actual CREATE TABLE and INSERT statements to avoid any confusion/issues.
              Then tried with XMLAGG and XMLELEMENT but giving me output in 1 row. I need proper XML format.
              That was valid XML format. There is no such thing as "proper XML format". I think what you are referring to is human-readable (line returns and indentions). Without them, the XML is smaller actually. A computer system does not care about whether the XML is human readable or not. If it does, that is a bug within that system.

              If you are still looking for making human readable XML for some reason, then look at [url http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions239.htm#SQLRF06231]XMLSerialize You would want something like
              SELECT XMLSERIALIZE(DOCUMENT XMLElement(...) AS CLOB indent size = 2) FROM ...