Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

outer joins while creating xml from database tables

user8195117Sep 14 2020 — edited Sep 15 2020

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/>

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/>

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 ignore
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),
          (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 ph1
WHERE 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>

1 - 7

Added on Sep 14 2020

#performance-availability, #xml-db

7 comments

567 views

Database Software

outer joins while creating xml from database tables

Comments

Post Details