Exclude blank columns from XML

I am trying to get Table Rows to XMLs. I am able to do it in the following way and the only issue I have is that it also generates XML tags for blank columns.

create table test_niks_01(x int, y varchar(10));

insert into test_niks_01 values(1, 'ABC');
INSERT INTO test_niks_01 values(2);
INSERT INTO test_niks_01 values(3, '');

--SelecT query_to_xml('SELECT * FROM test_niks_01', false, false, '');

select xmlelement(name "Root", xmlforest (x as X, y as Y));

Output XML:

<Root><x>1</x><y>ABC</y></Root>
<Root><x>2</x></Root>
<Root><x>3</x><y></y></Root>

Expected XML:

<Root><x>1</x><y>ABC</y></Root>
<Root><x>2</x></Root>
<Root><x>3</x></Root>

Any help will be appreciated. Thanks

Answer

You could just use nullif() to turn empty strings to nulls in the second column:

select xmlelement(name "Root", xmlforest (x as X, nullif(y, '') as Y)) from test_niks_01

Demo on DB Fiddle:

| xmlelement                      |
| :------------------------------ |
| <Root><x>1</x><y>ABC</y></Root> |
| <Root><x>2</x></Root>           |
| <Root><x>3</x></Root>           |

Advertisement

Answer