Thread: query_to_xml() returns invalid XML when query returns no rows

I am not sure if this qualifies as a bug: 

query_to_xml() returns an empty XML document when the query returns no rows, e.g:

   select query_to_xml('select 42 where false', false, true, '');

The problem with this is, that if the resulting XML is then fed into e.g. the xpath() function, that function fails
becausethe "empty" document is an invalid XML:
 

So the following query:

    select xpath('/row/col/text()', query_to_xml('select 42 as col where false', false, true, ''));

fails with "ERROR:  could not parse XML document"

I would have expected query_to_xml() to return NULL if the query returns no rows, rather than an invalid XML document.

Note that IS DOCUMENT is false for the empty XML returned, so it can be trapped in a query. 
But if query_to_xml() is nested somehow (like the above) this is really complicated to apply and I find it a bit
surprisingthat query_to_xml() returns invalid XML at all
 

Tested with 10.2 on Windows 10

Thomas

On 3/12/18 08:18, Thomas Kellerer wrote:
> I am not sure if this qualifies as a bug: 
> 
> query_to_xml() returns an empty XML document when the query returns no rows, e.g:
> 
>    select query_to_xml('select 42 where false', false, true, '');
> 
> The problem with this is, that if the resulting XML is then fed into e.g. the xpath() function, that function fails
becausethe "empty" document is an invalid XML:
 

That's because you have the tableforest argument set to true.  If you
want a proper XML document, then you should write

    select query_to_xml('select 42 where false', false, false, '');

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter Eisentraut schrieb am 12.03.2018 um 23:31:
>> I am not sure if this qualifies as a bug:
>>
>> query_to_xml() returns an empty XML document when the query returns no rows, e.g:
>>
>>     select query_to_xml('select 42 where false', false, true, '');
>>
>> The problem with this is, that if the resulting XML is then fed
>> into e.g. the xpath() function, that function fails because the
>> "empty" document is an invalid XML:
> 
> That's because you have the tableforest argument set to true.  If you
> want a proper XML document, then you should write
> 
>      select query_to_xml('select 42 where false', false, false, '');
> 

Hmm, that indeed works.
I didn't want the extra level introduced by the <table> tag, that's why I used tableforest = true.
But that's easier to deal with than the check for an invalid document

I still think it's incorrect to return an empty (=invalid) XML instead of a NULL value though.

Regards
Thomas
  

On 3/13/18 15:21, Thomas Kellerer wrote:
> I still think it's incorrect to return an empty (=invalid) XML instead of a NULL value though.

This behavior is specified in the SQL standard.  While an empty string
is not a valid XML "document", it is valid as XML "content".

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services