I need your help to solve the below problem which i have. I am using SQL/XML functions to create XML files from database tables. Below is the table structure with sample data and code snippet for creating XML CLOB. I need to exclude the element if it doesn't have a value for the column.
- Code: Select all
create table test_table (key number(20), name varchar2(50), description varchar2(100));
insert into test_table (key, name, description) values (1,'sam','desc1');
insert into test_table (key, name, description) values (2,'max','desc2');
insert into test_table (key, name, description) values (3,'peter',null);
insert into test_table (key, name, description) values (4,'andrew',null);
commit;
select
XMLROOT(
xmlelement("Root",
xmlagg(
xmlelement("Cat",
xmlelement("Name",name),
xmlelement("Desc",description)
)
)
)).getClobVal()
from test_table;
My desired xml output should not contain the element description for keys 3 & 4 as it description is null. Also please suggest if we use case statements here to exclude an element for certain conditions.
