Create xml from query if query contains records

Question

I'm trying to create some xml based on a query in Oracle SQL. This is pretty easy if you do a 1 to 1 conversion, however I need to make a grouping element around the returned xml. This is the working code I have.

WITH table2 AS (
  SELECT t.id
  FROM   table1 t
  WHERE t.code = 'somevalue')

select xmlelement("mes:tests",
    xmlattributes('http://testnamespace.com/' as "xmlns:tns"),
    xmlagg(xmlelement("tns:test",
        xmlelement("tns:ID", id))))
from table2;

The problem is that in this case it returns an empty tests-element even if there is no data. I need it to return NULL. I've tried several things so far. For example this case:

WITH table2 AS (
      SELECT t.id
      FROM   table1 t
      WHERE t.code = 'somevalue')

select CASE WHEN exists(select id from table2) THEN 
 xmlelement("mes:tests",
        xmlattributes('http://testnamespace.com/' as "xmlns:tns"),
        xmlagg(xmlelement("tns:test",
            xmlelement("tns:ID", id)))) ELSE null end
    from table2;

However this gives an error:

not a single-group group function

Anyone know decent way I can do this? I'm using Oracle DB 12c.

Answer 1

Based on your description you can change the exists check to a count:

select CASE WHEN count(id) > 0 THEN 
 xmlelement("mes:tests",
        xmlattributes('http://testnamespace.com/' as "xmlns:tns"),
        xmlagg(xmlelement("tns:test",
            xmlelement("tns:ID", id)))) ELSE null end
    from table2;

Demo with another CTE for sample data, with no matches:

set null "(null)"

WITH table1(id, code) as (
      select 42, 'ignore' from dual),
table2 AS (
      SELECT t.id
      FROM   table1 t
      WHERE t.code = 'somevalue')

select CASE WHEN count(id) > 0 THEN 
 xmlelement("mes:tests",
        xmlattributes('http://testnamespace.com/' as "xmlns:tns"),
        xmlagg(xmlelement("tns:test",
            xmlelement("tns:ID", id)))) ELSE null end
    from table2;

CASEWHENCOUNT(ID)>0THENXMLELEMENT("MES:TESTS",XMLATTRIBUTES('HTTP://TESTNAMESPAC
--------------------------------------------------------------------------------
(null)

and with matches:

WITH table1(id, code) as (
      select 42, 'somevalue' from dual union all
      select 43, 'somevalue' from dual),
table2 AS (
      SELECT t.id
      FROM   table1 t
      WHERE t.code = 'somevalue')

select CASE WHEN count(id) > 0 THEN 
 xmlelement("mes:tests",
        xmlattributes('http://testnamespace.com/' as "xmlns:tns"),
        xmlagg(xmlelement("tns:test",
            xmlelement("tns:ID", id)))) ELSE null end
    from table2;

CASEWHENCOUNT(ID)>0THENXMLELEMENT("MES:TESTS",XMLATTRIBUTES('HTTP://TESTNAMESPAC
--------------------------------------------------------------------------------
<mes:tests xmlns:tns="http://testnamespace.com/"><tns:test><tns:ID>42</tns:ID></
tns:test><tns:test><tns:ID>43</tns:ID></tns:test></mes:tests>