Postgresql - XML to string conversion issue

Question

Here is my query:

select XMLSERIALIZE(query_to_xml('select * from foo order by field1, field2 offset 1 limit 2', true, false,'') as character varying)

I receive this error:

ERREUR:  erreur de syntaxe sur ou près de « query_to_xml »
LINE 1: select XMLSERIALIZE(query_to_xml('select * from foo ord...
                        ^

********** Erreur **********

ERREUR: erreur de syntaxe sur ou près de « query_to_xml »
État SQL :42601
Caractère : 21

Translation from french to english :
erreur de syntaxe sur ou près de(...) is equivalent to syntax error near(...)

What am I doing wrong ?

EDIT:
I tried those two syntaxes too with no luck :

select XMLSERIALIZE(DOCUMENT query_to_xml('select * from foo ord
select XMLSERIALIZE(CONTENT query_to_xml('select * from foo ord

Postgresql 8.3

Stephan · Accepted Answer · 2012-10-23 14:45:59Z

5

Here how I solve my problem. I failed to indicate the casting type.

Either syntaxes work:

select XMLSERIALIZE(DOCUMENT query_to_xml('select * from foo order by 1', false, false,'') as text)

select XMLSERIALIZE(DOCUMENT query_to_xml('select * from foo order by 1', false, false,'') as varchar)

Nota: I could replace DOCUMENT with CONTENT and it works like a charm.

answered Oct 23, 2012 at 14:45

Stephan

43.2k69 gold badges245 silver badges342 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Clodoaldo Neto Over a year ago

Then my suggestion was not wrong. You indeed needed the document/content words.

Stephan Over a year ago

@Clodoaldo Sure but your suggestion at first was just a cut/paste of Postgresql doc and not a specific answer to my initial problem. BTW your suggestion help me greatly for finding what's was wrong in my query. Thanks !

Clodoaldo Neto · Accepted Answer · 2012-10-19 16:48:27Z

0

select XMLSERIALIZE({ DOCUMENT | CONTENT } query_to_xml(

answered Oct 19, 2012 at 16:48

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Postgresql - XML to string conversion issue

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related