outer joins while creating xml from database tables

user8195117

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

Jason_(A_Non)

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

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)

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

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

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)

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

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>