We have a 11.2 DB.
We have a to generate a huge XML file (with 100s of tags) and store it in a CLOB column in a table.
When generating the XML we ran into the problem of XMLFOREST not generating empty tags (like <A></A>).
When the value is NULL, XMLFOREST will not generate any tag.
I have simplified our problem by creating this XML output:
-- This is the structure
<A>
<B>
<C> john </C>
<D> 90210 </D>
<E> date1 </E>
<F>
<G> robert </G>
</F>
</B>
</A>
-- Works
SELECT XMLELEMENT
("A",
XMLELEMENT
("B",
XMLFOREST(
'john' as "C",
90210 as "D",
'date1' as "E")
,XMLELEMENT
("F",
XMLELEMENT("G", 'robert')
)
)
)
FROM dual;
Output: <A><B><C>john</C><D>90210</D><E>date1</E><F><G>robert</G></F></B></A>
-- With NULL for "D"
SELECT XMLELEMENT
("A",
XMLELEMENT
("B",
XMLFOREST(
'john' as "C",
NULL as "D",
'date1' as "E")
,XMLELEMENT
("F",
XMLELEMENT("G", 'robert')
)
)
)
FROM dual;
Output: <A><B><C>john</C><E>date1</E><F><G>robert</G></F></B></A> As you can see, tag "D" is not generated.
According to this post if field null don't construct a xmlelement or attribute, how? (9i) , in the last entry, it says that you have to use XMLELEMENT if you want to print NULL tags.
Problem is, if we use XMLELEMENT, we make our query very complicated and readability, debugging and maintenance will be very difficult since have to use one XMLELEMENT for each entry in the XMLFOREST.
In the above scenarios , it is possible to give a CASE and branch the "D"?? I tried but failed.
How can we deal with generating very large XML files with 100s of tags where only few tags are mapped to NULLABLE columns in the DB?
We want a solution to this problem. All the tag values for the Cs are in 1 table, and all the tag values for Es are in another table:
<A>
<B>
<C1> Mapped to not null column</C1>
<C2> Mapped to not null column</C2>
<C3> Mapped to not null column</C3>
<C4> Mapped to NULLABLE column</C4>
<C5> Mapped to not null column</C5>
<C6> Mapped to not null column</C6>
<C7> Mapped to NULLABLE column</C7>
<C8> Mapped to not null column</C8>
</B>
<D>
<C9>
<E1>Mapped to not null column<E1>
<E2>Mapped to not null column<E2>
<E3>Mapped to not null column<E3>
<E4>Mapped to not null column<E4>
<E5>Mapped to not null column<E5>
<E6>Mapped to NULLABLE column<E6>
</C9>
</D>
</A>
Any solution will be greatly appreciated.