1 Reply Latest reply: Jul 5, 2013 8:41 AM by odie_63 RSS

    Need help for SQL SELECT query to fetch XML records from Oracle tables having CLOB field

    778289ca-9270-4358-908f-c50fa691166c

      Hello,

       

      I have a scenario wherein i need to fetch records from several oracle tables having CLOB fields(which is holding XML) and then merge them logically to form a hierarchy XML. All these tables are related with PK-FK relationship. This XML hierarchy is having 'OP' as top-most root node and ‘DE’ as it’s bottom-most node with One-To-Many relationship. Hence, Each OP can have multiple GM, Each GM can have multiple DM and so on.

       

      Table structures are mentioned below:

       

      OP:

      Name                             Null                    Type        

      ----------------- -------- ------------ ----------------- -------- ------------

      OP_NBR                    NOT NULL      NUMBER(4)    (Primary Key)

      OP_DESC                                        VARCHAR2(50)

      OP_PAYLOD_XML                           CLOB       

       

      GM:

      Name                          Null                   Type        

      -------------- -------- ------------ -------------- -------- ------------

      GM_NBR                  NOT NULL       NUMBER(4)    (Primary Key)

      GM_DESC                                       VARCHAR2(40)

      OP_NBR               NOT NULL          NUMBER(4)    (Foreign Key)

      GM_PAYLOD_XML                          CLOB   

       

      DM:

      Name                          Null                    Type        

      -------------- -------- ------------ -------------- -------- ------------

      DM_NBR                  NOT NULL         NUMBER(4)    (Primary Key)

      DM_DESC                                         VARCHAR2(40)

      GM_NBR                  NOT NULL         NUMBER(4)    (Foreign Key)

      DM_PAYLOD_XML                            CLOB       

       

      DE:

      Name                          Null                    Type        

      --------------- -------- ------------ --------------- -------- ------------

      DE_NBR                     NOT NULL           NUMBER(4)    (Primary Key)

      DE_DESC                   NOT NULL           VARCHAR2(40)

      DM_NBR                    NOT NULL           NUMBER(4)    (Foreign Key)

      DE_PAYLOD_XML                                CLOB    

       

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

      SELECT

      j.op_nbr||'||'||j.op_desc||'||'||j.op_paylod_xml AS op_paylod_xml,

      i.gm_nbr||'||'||i.gm_desc||'||'||i.gm_paylod_xml AS gm_paylod_xml,

      h.dm_nbr||'||'||h.dm_desc||'||'||h.dm_paylod_xml AS dm_paylod_xml,

      g.de_nbr||'||'||g.de_desc||'||'||g.de_paylod_xml AS de_paylod_xml,

      FROM

      DE g, DM h, GM i, OP j

      WHERE

      h.dm_nbr = g.dm_nbr(+) and

      i.gm_nbr = h.gm_nbr(+) and

      j.op_nbr = i.op_nbr(+)

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

       

      I am using above SQL select statement for fetching the XML records and this gives me all related xmls for each entity in a single record(OP, GM, DM. DE). Output of this SQL query is as below:

       

      Current O/P:

      <resultSet>

           <Record1>

                <OP_PAYLOD_XML1>

                <GM_PAYLOD_XML1>

                <DM_PAYLOD_XML1>

                <DE_PAYLOD_XML1>

           </Record1>

           <Record2>

                <OP_PAYLOD_XML2>

                <GM_PAYLOD_XML2>

                <DM_PAYLOD_XML2>

                <DE_PAYLOD_XML2>

           </Record2>

      .

      .

      .

           <RecordN>

                <OP_PAYLOD_XMLN>

                <GM_PAYLOD_XMLN>

                <DM_PAYLOD_XMLN>

                <DE_PAYLOD_XMLN>

           </RecordN>

      </resultSet>

       

      Now i want to change my SQL query so that i get following output structure:

       

      <resultSet>

           <Record>

                <OP_PAYLOD_XML1>

                <GM_PAYLOD_XML1>

                <GM_PAYLOD_XML2> .......

                <GM_PAYLOD_XMLN>

                <DM_PAYLOD_XML1>

                <DM_PAYLOD_XML2> .......

                <DM_PAYLOD_XMLN>

                <DE_PAYLOD_XML1>

                <DE_PAYLOD_XML2> .......

                <DE_PAYLOD_XMLN>

           </Record>

           <Record>

                <OP_PAYLOD_XML2>

                <GM_PAYLOD_XML1'>

                <GM_PAYLOD_XML2'> .......

                <GM_PAYLOD_XMLN'>

                <DM_PAYLOD_XML1'>

                <DM_PAYLOD_XML2'> .......

                <DM_PAYLOD_XMLN'>

                <DE_PAYLOD_XML1'>

                <DE_PAYLOD_XML2'> .......

                <DE_PAYLOD_XMLN'>

           </Record>

      <resultSet>

       

      Appreciate your help in this regard!