2 Replies Latest reply: Mar 20, 2013 12:42 PM by AlbertoFaenza RSS

    Generating XML from Nested tables

    626673
      CREATE TYPE TEST_103 AS OBJECT(CHILD_CSE_ID VARCHAR2(25));
      CREATE TYPE TEST_104 AS TABLE OF TEST_103;

      CREATE TABLE TEST_105 (
      CL_ID VARCHAR2(25),
      CSE_ID VARCHAR2(14),
      EXP_ID VARCHAR2(13),
      CS_LST TEST_104
      )NESTED TABLE CS_LST STORE AS TEST_104_TAB;

      INSERT INTO TEST_105 values ('A','100','E_100',TEST_104(TEST_103('C_100'),TEST_103('C_105')));
      INSERT INTO TEST_105 values ('A','200','E_200',TEST_104(TEST_103('C_200'),TEST_103('C_205')));
      INSERT INTO TEST_105 values ('A','300','E_300',TEST_104(TEST_103('C_300'),TEST_103('C_305')));
      INSERT INTO TEST_105 values ('A','300',NULL,NULL);
      INSERT INTO TEST_105 values ('B','400','E_400',TEST_104(TEST_103('C_400'),TEST_103('C_405')));
      INSERT INTO TEST_105 values ('B','500','E_500',TEST_104(TEST_103('C_500'),TEST_103('C_505')));
      INSERT INTO TEST_105 values ('B','600','E_600',TEST_104(TEST_103('C_600'),TEST_103('C_605'),TEST_103('C_606')));
      COMMIT ;

      SELECT * FROM TEST_105;

      I want an XML output like as follows .
      <?xml version="1.0" encoding="UTF-8"?>
      <Main_case>
      <cl_id="A">
      <cse_id="100">
      <cs_lst>
      <case>C_100</case>
      <case>C_105</case>
      </cs_lst>
      </cse_id>
      <cse_id="200">
      <cs_lst>
      <case>C_200</case>
      <case>C_205</case>
      </cs_lst>
      </cse_id>
      <cse_id="300">
      <cs_lst>
      <case>C_300</case>
      <case>C_305</case>
      </cs_lst>
      </cse_id>
      </cl_id>

      <cl_id="B">
      <cse_id="400">
      <cs_lst>
      <case>C_400</case>
      <case>C_405</case>
      </cs_lst>
      </cse_id>
      <cse_id="500">
      <cs_lst>
      <case>C_500</case>
      <case>C_505</case>
      </cs_lst>
      </cse_id>
      <cse_id="300">
      <cs_lst>
      <case>C_600</case>
      <case>C_605</case>
      <case>C_606</case>
      </cs_lst>
      </cse_id>
      </cl_id>
      </Main_case>



      How to get this any suggestions to achive this will help a lot.


      Thanks
      Rangan Sampath
        • 1. Re: Generating XML from Nested tables
          Most Wanted!!!!
          Example
          SELECT DBMS_XMLGEN.getxml
                    ('select * from (SELECT ''abdul'' name, 20000 salary FROM DUAL
          UNION ALL
          SELECT ''jp'', 10000 FROM DUAL
          UNION ALL
          SELECT ''yok'', 30000 FROM DUAL
          UNION ALL
          SELECT ''gopi'', 40000 FROM DUAL
          UNION ALL
          SELECT ''xavi'', 5000 FROM DUAL)'
                    ) xmlfmt
            FROM DUAL;
          WITH t AS
               (SELECT 'abdul' NAME, 20000 sal
                  FROM DUAL
                UNION ALL
                SELECT 'jp', 10000
                  FROM DUAL
                UNION ALL
                SELECT 'yok', 30000
                  FROM DUAL
                UNION ALL
                SELECT 'gopi', 40000
                  FROM DUAL
                UNION ALL
                SELECT 'xavi', 5000
                  FROM DUAL)
          SELECT XMLELEMENT ("EMP",
                             XMLELEMENT ("NAME", NAME),
                             XMLELEMENT ("SALARY", sal)
                            )
            FROM t;
          Regards,
          friend

          Edited by: most wanted!!!! on Mar 20, 2013 9:26 AM
          • 2. Re: Generating XML from Nested tables
            AlbertoFaenza
            Hi Rangan,

            thank you for posting CREATE TABLE and INSERT statements.

            Please when you put some code or output please enclose it between two lines starting with {noformat}
            {noformat}
            
            i.e.:
            {noformat}
            {noformat}
            SELECT ...
            {noformat}
            {noformat}
            
            Your output does not seem to be valid XML.
            
            If you try to validate your XML (check some online <a href="http://www.w3schools.com/xml/xml_validator.asp">XML validator</a>) these parts will not be valid
            <cl_id="A">
            <cse_id="100">
            I just posted 2 lines but all of them are not valid.
            You probably have to create something like this:
            <cl_id attribute_name="A">
            <cse_id attribute_name="100">
            You may replace attribute_name with whatever you prefer in your output.
            
            Regards.
            Al