I'm digging google for the last few days and stuck without sulution.
Main problem is, when I construct XML content from directly from SQL, there are no empty XML nodes generated for null values.
id_msg as ID,
datepar AS Date,
xml_subnode as aaa
subid AS subid
) AS Subtree
When SELECT XMLAGG... returns no items, no Subtree node is placed into XML_node. It should be represented as '<Subtree />' but the is none.
The same about simply values. If datepar is NULL, no Date XML item will be present in final XML document.
I found I can build XML ducument using XMLELEMENT in iteration loop concatenating result elements - XMLELEMENT for single field returns nulls in correct way. But how to do that SELECTING data into SQL?
EDIT: Further investigation shown that XMLFOREST function makes null elements missing.
where SUB contains SELECT id2 FROM t2 WHERE t2.id2=t1.id2
id1 as id1,
fielda as fielda
id2 AS id2
) AS Sub
Problem is when there is no related records in t2 for given t1.id2. In that case there will be no <sub /> tag. Sub node will be missing since SELECT FROM t2 will return no records.
Another examples which are related to the problem:
SELECT XMLELEMENT (name some_field, null);
-- works ok, returns <some_field />
SELECT XMLFOREST (
1 as some_field1,
null as some_field_null)
doesn't work as expected. Returns only <some_field1>1</some_field1>