2 Replies Latest reply: Dec 15, 2013 10:40 PM by Saxena RSS

    Need to select XMLTYPE data in unindented form, from a query.

    Saxena

      I need to select XMLTYPE data among relational data as an xml element.

      SELECT

        XMLSERIALIZE(DOCUMENT

        XMLELEMENT ("Account",

            XMLATTRIBUTES (LPAD(ROWNUM, 10 , '0')        AS "recordId"    )

             ,XMLELEMENT ("Header"

                 ,XMLELEMENT ("AccountId"

                    ,XMLELEMENT ("AccountNumber"          ,car.acc_nbr              )))

                    ,XMLELEMENT ("CreditPurpose"            ,car.credit_purp           )

                   ,XMLELEMENT ("AccountType"              ,car.acc_type              )

                  , (cdx.cus_dtls)  -- /*THIS IS XMLTYPE data*/

        ))AS crdtrpt

      FROM  cus_acc_rpt car , cus_dtls_xml cdx

      WHERE car.ar_id = cdx.ar_id

      Here cdx.cus_dtls need to be selected in unindented form (each records as a long unbroken string of data). So that I can SPOOL huge number of records in lesser time.

       

      I am trying to convert this into CLOB and Insert into CLOB type column of a table.

      But XMLSERIALIZE does not seem to serialize the xml data in cus_Dtls column.

      However it does serialize the xml typ data selected other than cus_dtls.

       

      Any quick hints to meet the requirements will be very welcomed !!

       

      Cheers !!

      Saxena