Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
outer joins while creating xml from database tables

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
-
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?
-
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>
-
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.
-
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);
-
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
-
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.
-
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>