4 Replies Latest reply on May 3, 2012 3:32 PM by Jason_(A_Non)

    couldnt produce XML format via one detail table.

    888157
      Hi guys,

      I have a small problem about producing an xml data. I created an example for easy understand.

      This is my main table:
        CREATE TABLE ALLDATA
        (
          AGENCY_NO       VARCHAR2(20),
           ACENCY_NAME     VARCHAR2(500),
           SUB_AGENCY_NO   VARCHAR2(20),
           SUB_AGENCY_NAME VARCHAR2(500),
           CUST_ID         VARCHAR2(20),
           CUST_NAME       VARCHAR2(500),
           CUST_STREET     VARCHAR2(500),
           CUST_CITY       VARCHAR2(500),
           POLICY_NO       NUMBER(10),
           SUB_POLICY_NO   NUMBER,
           POLICY_DATE     DATE,
           POLICY_CURRENCY VARCHAR2(3)
        );
        
        INSERT INTO ALLDATA 
        VALUES (10, 'X AGENCY', 100, 'X SUB AGENCY 1', 1000, 'X CUSTOMER 1', 'ADRES INFO', 'A CITY', 123456, 0, SYSDATE, 'USD');
          INSERT INTO ALLDATA 
        VALUES (10, 'X AGENCY', 100, 'X SUB AGENCY 1', 1000, 'X CUSTOMER 1', 'ADRES INFO', 'A CITY', 123456, 1, SYSDATE, 'USD');
        INSERT INTO ALLDATA 
        VALUES (10, 'X AGENCY', 100, 'X SUB AGENCY 1', 1002, 'X CUSTOMER 2', 'ADRES INFO', 'A CITY', 654312, 0, SYSDATE, 'USD');
        INSERT INTO ALLDATA 
        VALUES (10, 'X AGENCY', 101, 'X SUB AGENCY 2', 1003, 'X CUSTOMER 3', 'ADRES INFO', 'A CITY', 234567, 0, SYSDATE, 'USD');
        INSERT INTO ALLDATA 
        VALUES (10, 'X AGENCY', 101, 'X SUB AGENCY 2', 1003, 'X CUSTOMER 3', 'ADRES INFO', 'A CITY', 234567, 1, SYSDATE, 'USD');
        INSERT INTO ALLDATA 
        VALUES (11, 'Y AGENCY', 102, 'Y SUB AGENCY 3', 1004, 'X CUSTOMER 4', 'ADRES INFO', 'A CITY', 345678, 0, SYSDATE, 'USD');
        INSERT INTO ALLDATA 
        VALUES (11, 'Y AGENCY', 102, 'Y SUB AGENCY 3', 1004, 'X CUSTOMER 4', 'ADRES INFO', 'A CITY', 135790, 0, SYSDATE, 'USD');
        INSERT INTO ALLDATA 
        VALUES (11, 'Y AGENCY', 104, 'X SUB AGENCY 4', 1005, 'X CUSTOMER 5', 'ADRES INFO', 'A CITY', 112233, 0, SYSDATE, 'USD');
      
      
        COMMIT;
      Here what i need and what i did:

      this is example xml for this data
      <agency>
        <agency_info>
          <agency_no>10</agency_no>
          <agency_name>X AGENCY</agency_name>
        </agency_info>
          <sub_agency>
            <sub_agency_info>
              <sub_agency_no>100</sub_agency_no>
              <sub_agency_name>X SUB AGENCY 1</sub_agency_name>
            </sub_agency_info>
            
            <cust_policy>
              <cust_info>
                <cust_id>1000</cust_id>
                <cust_name>X CUSTOMER 1</cust_name>
              </cust_info>
          
              <policies>
                <policy_no>123456</policy_no>
                <sub_policy_no>0</sub_policy_no>
                <policy_date>2012-05-03</policy_date>
              </policies>
              <policies>
                <policy_no>123456</policy_no>
                <sub_policy_no>1</sub_policy_no>
                <policy_date>2012-05-03</policy_date>
              </policies>
            
            </cust_policy>
            
            <cust_policy>
              <cust_info>
                <cust_id>1002</cust_id>
                <cust_name>X CUSTOMER 2</cust_name>
              </cust_info>
          
              <policies>
                <policy_no>654312</policy_no>
                <sub_policy_no>0</sub_policy_no>
                <policy_date>2012-05-03</policy_date>
              </policies>
            </cust_policy>
          
          </sub_agency>
          <sub_agency>
            <sub_agency_info>
              <sub_agency_no>101</sub_agency_no>
              <sub_agency_name>X SUB AGENCY 2</sub_agency_name>
            </sub_agency_info>
             -- cust and policy
               --cust
                  --policy1
                  --policy2
                --cust2
                  --policy3
                  --policy4
          </sub_agency>
      </agency>
      
      <agency>
        <agency_info>
          <agency_no>11</agency_no>
          <agency_name>Y AGENCY</agency_name>
        </agency_info>
        ...
        ...
      </agency>
      I couldnt write full example but first part is enough i guess. for every agency's sub agency's customer's policies info must be listed like that.

      I tried group per agency and then XMLELEMENT and XMLAGG functions but couldnt create policy and cust info. So I put distinct agency info to another table, distinct sub_agency info to another table and customer info to another table and write an sql with subqueries: first select agency info, then a subquery which select sub_agency info in that subquery, i wrote another subquery that select cust info and another sub select policy info but performance is awful.

      is it possible to get this select just using that detail table ?

      ps: this is not my real date because of security issues. I created an example, so there may be small mistakes sorry for that.

      thanks a lot for help.
        • 1. Re: couldnt produce XML format via one detail table.
          odie_63
          Hi,

          Thanks for the sample data, that's very helpful.

          This will produce two rows in the format you require.
          If you need a single XML fragment, you can just add another XMLAgg.
          SELECT XMLElement("agency",
                   XMLElement("agency_info",
                     XMLForest(
                       agency_no as "agency_no"
                     , agency_name as "agency_name"
                     )
                   )
                 , XMLAgg(sub_agency)
                 ) as agency
          FROM (
            SELECT agency_no, agency_name
                 , XMLElement("sub_agency",
                     XMLElement("sub_agency_info",
                       XMLForest(
                         sub_agency_no as "sub_agency_no"
                       , sub_agency_name as "sub_agency_name"
                       )
                     )
                   , XMLAgg(cust_policy)
                   ) as sub_agency
            FROM (
              SELECT agency_no, agency_name
                   , sub_agency_no, sub_agency_name
                   , XMLElement("cust_policy",
                       XMLElement("cust_info",
                         XMLForest(
                           cust_id as "cust_id"
                         , cust_name as "cust_name"
                         )
                       )
                     , XMLAgg(
                         XMLElement("policies",
                           XMLForest(
                             policy_no as "policy_no"
                           , sub_policy_no as "sub_policy_no"
                           , policy_date as "policy_date"
                           )
                         )
                       )
                     ) as cust_policy
              FROM alldata
              GROUP BY agency_no, agency_name
                     , sub_agency_no, sub_agency_name
                     , cust_id, cust_name, cust_street, cust_city
            )
            GROUP BY agency_no, agency_name
                   , sub_agency_no, sub_agency_name
          )
          GROUP BY agency_no, agency_name
          ;
          • 2. Re: couldnt produce XML format via one detail table.
            888157
            thanks a lot for quick reply Odie_63, I will try and inform about the result immediately
            • 3. Re: couldnt produce XML format via one detail table.
              888157
              odie_63, thats works perfect for me, i am going to mark as correct your answer but i have a small problem, I'm inserting this data to a table but some special chars become irrelevent ascii chars like 'Ş' or 'İ' (another language chars). my database is support this char set but when i run

              create table as select ... (your sql here)

              chars become wrong, what can i do for this ?

              ps: result of select statement chars are fine but after insert they changed

              Edited by: elcaro on May 3, 2012 5:01 AM

              EDIT:

              SOLVED, i created my table as "(x XMLTYPE) xmltype column x STORE AS CLOB". store as clob solved it. thanks a lot odie.
              • 4. Re: couldnt produce XML format via one detail table.
                Jason_(A_Non)
                What version of Oracle?
                select * from v$version;

                Also what happens when you create the table as
                (x XMLTYPE) xmltype column x STORE AS SECUREFILE BINARY XML

                I ask because STORE AS CLOB is deprecated in the latest release of Oracle and the above is the default.

                What's the DB character set?