SQL Select empty

user_2438094 0
Viewed 100

I have the following code in Oracle 11:

select xmlelement("foo", xmlagg(xmlelement("bar",myValues))) from someTable where rownum = 0; --Changing the rownum from 0 should give me values

The output currently is: <foo></foo>

I would instead like this to return nothing, or null, when no rows are select. Otherwise it'll be a XMLtype with the aggregated data like how I have it above.

How would I be able to achieve this for the case when no rows are selected?

oracle sql
2 Answers

You can convert to CLOB using getClobval function and do a comparison.

SELECT
    xml
FROM
    (
        SELECT
            xmlelement("foo",xmlagg(xmlelement("bar",myvalues) ) ).getclobval() xml
        FROM
            sometable
    )
WHERE
    TO_CHAR(xml) != '<foo></foo>';
user_79985910

You can use case select:

select case when count (*) != 0 then xmlelement("foo", xmlagg(xmlelement("bar",myValues))) end

user_42887300
Related