Forum Stats

  • 3,837,949 Users
  • 2,262,310 Discussions
  • 7,900,436 Comments

Discussions

outer joins while creating xml from database tables

user8195117
user8195117 Member Posts: 56 Red Ribbon
edited Sep 15, 2020 6:19PM in XML DB

Hi ALL,

Below is the sample xml query used to create XML from database tables. When there are no records in the child tables...I would still require records to be printed in the XML. For example..tab3 does not have matching record... but still I need null tags to be part of the XML. Can you please help me here.

SELECT XMLELEMENT (

          "PH1",

          XMLELEMENT ("PH1_SEQ_NUM", 'PH1_' || ph1.ph1_seq_num),

          XMLELEMENT (

             "PH1_RECORD_ID",

             XMLATTRIBUTES ('N' AS "Error_Flag", 'N' AS "Error_Msg"),

             ph1.ph1_record_id),

          (SELECT XMLAGG (

                     XMLELEMENT (

                        "PH2",

                        XMLELEMENT ("PH2_SEQ_NUM",

                                    'PH2_' || ph2.ph2_seq_num),

                        XMLELEMENT (

                           "PH2_RECORD_ID",

                           XMLATTRIBUTES ('N' AS "Error_Flag",

                                          'N' AS "Error_Msg"),

                           ph2.ph2_record_id))

                     ORDER BY ph2.ph2_seq_num)

             FROM tab2 ph2

            WHERE ph1.ph1_seq_num = ph2.ph2_ph1_seq_num),

          (SELECT XMLAGG (

                     XMLELEMENT (

                        "PH3",

                        XMLELEMENT ("PH3_SEQ_NUM",

                                    'PH3_' || ph3.ph3_seq_num),

                        XMLELEMENT (

                           "PH3_RECORD_ID",

                           XMLATTRIBUTES ('N' AS "Error_Flag",

                                          'N' AS "Error_Msg"),

                           ph3.ph3_record_id))

                     ORDER BY ph3.ph3_seq_num)

             FROM tab3 ph3

            WHERE ph1.ph1_seq_num = ph3.ph3_ph1_seq_num))

          AS xml_data

  FROM tab1 ph1

WHERE ph1_seq_num = 626713;

Database Version

Oracle Database 11g Release 11.2.0.4.0 - 64bit

PL/SQL Release 11.2.0.4.0

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,106 Silver Trophy
    edited Sep 14, 2020 11:39AM

    As a reminder, this is a forum of volunteers from around the world.  When you first posted, it was the middle of the night in North/South America and early in Europe.

    Are you wanting to just see

    <PH3/>

    or

    <PH3><PH3_SEQ_NUM/><PH3_RECORD_ID/></PH3>

    The next question would be why do you need to see the null tag?

  • user8195117
    user8195117 Member Posts: 56 Red Ribbon
    edited Sep 14, 2020 11:46AM

    Hi Jason,

    I would like to see... as below

    <PH3>

    <PH3_SEQ_NUM></PH3_SEQ_NUM>

    <PH3_RECORD_ID Error_Flag="N" Error_Msg="N"></PH3_RECORD_ID>

    </PH3>

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,106 Silver Trophy
    edited Sep 14, 2020 12:23PM

    I'd started down this path

    SELECT XMLELEMENT("PH1",         XMLELEMENT("HI",'node data'),         (SELECT /*+NO_XML_QUERY_REWRITE*/                 XMLAGG (                   XMLELEMENT (                      "PH3",                      XMLELEMENT ("PH3_SEQ_NUM",                                  CASE WHEN ph3_seq_num IS NOT NULL THEN 'PH3_' || ph3_seq_num ELSE NULL END),                      XMLELEMENT (                         "PH3_RECORD_ID",                         XMLATTRIBUTES ('N' AS "Error_Flag",                                        'N' AS "Error_Msg"),                         ph3_record_id))                   ORDER BY ph3_seq_num)           FROM (SELECT case_no ph3_record_id, cocc_id_seq ph3_seq_num                   FROM my_dummy_table ph3                WHERE ph1.ph1_seq_num = ph3.ph3_ph1_seq_num                  UNION ALL                   SELECT NULL, NULL                    FROM dual                   WHERE NOT EXISTS (SELECT NULL                                        FROM my_dummy_table ph3                                       WHERE ph1.dummy = ph3.ph3_ph1_seq_num))))  FROM dual ph1;

    but the concept stops working because ph1.dummy is nested too deep to be referenced from within the NOT EXISTS.

    I was using this thread as a basis.

    Returning a default value in SQL when no rows found

    You may want to investigate the approach shown by BlueShadow or someone else in that thread.

    I've ran out of time for now to work on this, hence the reason I'm posting what I've tried so far.

  • user8195117
    user8195117 Member Posts: 56 Red Ribbon
    edited Sep 15, 2020 6:13AM

    Hi,

    I coded the query as below... But when there is no data in tab3... after adding xmlagg.. it returns 2 rows.. which is incorrect. The NULL needs to be excluded.

    SELECT XMLAGG (

              XMLELEMENT (

                 "PH3",

                 XMLELEMENT ("PH3_SEQ_NUM", 'PH3_' || ph3.ph3_ph1_seq_num),

                 XMLELEMENT (

                    "PH3_RECORD_ID",

                    XMLATTRIBUTES ('N' AS "Error_Flag", 'N' AS "Error_Msg"))))

              AS XML_DATA

      FROM tab3 ph3

    WHERE ph3.PH3_PH1_SEQ_NUM = 629175

    UNION ALL

    SELECT XMLELEMENT (

              "PH3",

              XMLELEMENT ("PH3_SEQ_NUM", 'PH3_' || 1),

              XMLELEMENT (

                 "PH3_RECORD_ID",

                 XMLATTRIBUTES ('N' AS "Error_Flag", 'N' AS "Error_Msg")))

              AS xml_data

      FROM DUAL

    WHERE NOT EXISTS

              (SELECT NULL, NULL

                 FROM tab3 ph3

                WHERE ph3.PH3_PH1_SEQ_NUM = 629175);

  • user8195117
    user8195117 Member Posts: 56 Red Ribbon
    edited Sep 15, 2020 1:17PM

    Because... the above mentioned query is returning 2 rows... the below query is throwing error "ORA-01427: single-row subquery returns more than one row" on tab3.

    SELECT XMLELEMENT (

              "PH1",

              XMLELEMENT ("PH1_SEQ_NUM", 'PH1_' || ph1.ph1_seq_num),

              XMLELEMENT (

                 "PH1_RECORD_ID",

                 XMLATTRIBUTES ('N' AS "Error_Flag", 'N' AS "Error_Msg"),

                 ph1.ph1_record_id),

              (SELECT XMLAGG (

                         XMLELEMENT (

                            "PH2",

                            XMLELEMENT ("PH2_SEQ_NUM",

                                        'PH2_' || ph2.ph2_seq_num),

                            XMLELEMENT (

                               "PH2_RECORD_ID",

                               XMLATTRIBUTES ('N' AS "Error_Flag",

                                              'N' AS "Error_Msg"),

                               ph2.ph2_record_id))

                         ORDER BY ph2.ph2_seq_num)

                 FROM tab2 ph2

                WHERE ph1.ph1_seq_num = ph2.ph2_ph1_seq_num),

              (SELECT XMLAGG (

                         XMLELEMENT (

                            "PH3",

                            XMLELEMENT ("PH3_SEQ_NUM",

                                        'PH3_' || ph3.ph3_seq_num),

                            XMLELEMENT (

                               "PH3_RECORD_ID",

                               XMLATTRIBUTES ('N' AS "Error_Flag",

                                              'N' AS "Error_Msg"), ph3.ph3_record_id))

                         ORDER BY ph3.R879_PH3_SEQ_NUM)

                 FROM tab3 ph3

                WHERE ph1.ph1_seq_num = ph3.PH3_PH1_SEQ_NUM

               UNION ALL

               SELECT XMLELEMENT (

                         "PH3",

                         XMLELEMENT ("PH3_SEQ_NUM", 'PH3_' || 1),

                         XMLELEMENT (

                            "PH3_RECORD_ID",

                            XMLATTRIBUTES ('N' AS "Error_Flag",

                                           'N' AS "Error_Msg"),'PH3'))

                 FROM DUAL

                WHERE NOT EXISTS

                         (SELECT NULL

                            FROM tab3 ph3

                           WHERE ph1.ph1_seq_num = ph3.PH3_PH1_SEQ_NUM)))

      FROM tab1 ph1

    WHERE ph1_seq_num = 627659;

    If this is a problem... is there any other way to get empty tags

    as suggested

    <PH3/>

    or

    <PH3><PH3_SEQ_NUM/><PH3_RECORD_ID/></PH3>

    ph3.r879_ph3_record_id

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,106 Silver Trophy
    edited Sep 15, 2020 3:07PM

    I ended up using COALESCE to come up with

    WITH tab3_def AS (SELECT 1 ph3_ph1_seq_num, 2 ph3_seq_num, 3 ph3_record_id FROM dual),     tab3 AS (SELECT * FROM tab3_def WHERE 1=2),     tab1 AS (SELECT 626713 ph1_seq_num, 'A' ph1_record_id FROM dual),     tab2 AS (SELECT 626713 AS ph2_ph1_seq_num, 't2ri' ph2_record_id, 1 ph2_seq_num FROM dual)-- Above used to simulate your tables in my system, you can ignoreSELECT XMLELEMENT (          "PH1",          XMLELEMENT ("PH1_SEQ_NUM", 'PH1_' || ph1.ph1_seq_num),          XMLELEMENT (             "PH1_RECORD_ID",             XMLATTRIBUTES ('N' AS "Error_Flag", 'N' AS "Error_Msg"),             ph1.ph1_record_id),          (SELECT XMLAGG (                     XMLELEMENT (                        "PH2",                        XMLELEMENT ("PH2_SEQ_NUM",                                    'PH2_' || ph2.ph2_seq_num),                        XMLELEMENT (                           "PH2_RECORD_ID",                           XMLATTRIBUTES ('N' AS "Error_Flag",                                          'N' AS "Error_Msg"),                           ph2.ph2_record_id))                     ORDER BY ph2.ph2_seq_num)             FROM tab2 ph2            WHERE ph1.ph1_seq_num = ph2.ph2_ph1_seq_num),          (COALESCE((SELECT XMLAGG (                     XMLELEMENT (                        "PH3",                        XMLELEMENT ("PH3_SEQ_NUM",                                    'PH3_' || ph3.ph3_seq_num),                        XMLELEMENT (                           "PH3_RECORD_ID",                           XMLATTRIBUTES ('N' AS "Error_Flag",                                          'N' AS "Error_Msg"),                           ph3.ph3_record_id))                     ORDER BY ph3.ph3_seq_num)             FROM tab3 ph3            WHERE ph1.ph1_seq_num = ph3.ph3_ph1_seq_num), XMLTYPE('<PH3><PH3_SEQ_NUM/><PH3_RECORD_ID Error_Flag="N" Error_Msg="N"/></PH3>'))))          AS xml_data  FROM tab1 ph1WHERE ph1.ph1_seq_num = 626713;

    As you can see, I used COALESCE and hard-coded in the desired XML fragment you want included when there is no data in ph3.

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Sep 15, 2020 6:19PM

    with

      tab1(ph1_seq_num, ph1_record_id) as (

        select 626713, 'A' from dual union all

        select 626714, 'B' from dual

      ),

      tab2(ph2_ph1_seq_num, ph2_record_id, ph2_seq_num) as (

        select 626713, 't2ri', 1 from dual

      ),

      tab3(ph3_ph1_seq_num, ph3_seq_num, ph3_record_id) as (

        select 626714, 1, 2 from   dual

      )

    select XMLELEMENT ("PH1",

             XMLELEMENT ("PH1_SEQ_NUM", NVL2(PH1_seq_num, 'PH1_'||PH1_seq_num, null)),

             XMLELEMENT ("PH1_RECORD_ID",

               XMLATTRIBUTES ('N' AS "Error_Flag",

                              'N' AS "Error_Msg"),

               ph1_record_id

             )

             , X1

             , X2

           )       

    from (

      select ph1_seq_num, ph1_record_id,

             XMLAGG(

               XMLELEMENT ("PH2",

                 XMLELEMENT ("PH2_SEQ_NUM", NVL2(ph2_seq_num + 0, 'PH2_'||ph2_seq_num, null)),

                 XMLELEMENT ("PH2_RECORD_ID",

                   XMLATTRIBUTES ('N' AS "Error_Flag",

                                  'N' AS "Error_Msg"),

                   ph2_record_id

                 )

               )       

               ORDER BY ph2_seq_num     

             ) X1,

             XMLAGG (

               XMLELEMENT ("PH3",

                 XMLELEMENT ("PH3_SEQ_NUM", NVL2(ph3_seq_num + 0, 'PH2_'||ph3_seq_num, null)),

                 XMLELEMENT ("PH3_RECORD_ID",

                   XMLATTRIBUTES ('N' AS "Error_Flag",

                                  'N' AS "Error_Msg"),

                   ph3_record_id)

               )

               ORDER BY ph3_seq_num

             ) X2       

      from tab1 t1

      left join tab2 t2 on t2.ph2_ph1_seq_num = t1.ph1_seq_num

      left join tab3 t3 on t3.ph3_ph1_seq_num = t1.ph1_seq_num + 0  -- +0 prevents the optimiser mangling the left joins pushing predicates into aggregates

      where ph1_seq_num = 626713

      group by ph1_seq_num, ph1_record_id

    ) v

    <PH1>

      <PH1_SEQ_NUM>PH1_626713</PH1_SEQ_NUM>

      <PH1_RECORD_ID Error_Flag="N" Error_Msg="N">A</PH1_RECORD_ID>

      <PH2>

        <PH2_SEQ_NUM>PH2_1</PH2_SEQ_NUM>

        <PH2_RECORD_ID Error_Flag="N" Error_Msg="N">t2ri</PH2_RECORD_ID>

      </PH2>

      <PH3>

        <PH3_SEQ_NUM/>

        <PH3_RECORD_ID Error_Flag="N" Error_Msg="N"/>

      </PH3>

    </PH1>