Обсуждение: Using XMLNAMESPACES with XMLEMENT

Hi All,

 

I am attempting to port the following statement from DB2z to Postgres:

 

SELECT e.empno, e.firstnme, e.lastname,

          XMLELEMENT ( NAME "foo:Emp",

            XMLNAMESPACES('http://www.foo.com' AS "foo"),

            XMLATTRIBUTES(e.empno as "serial"),

                          e.firstnme,

                          e.lastname ) AS "Result"

       FROM EMP e

       WHERE e.edlevel = 12;

 

The NAMESPACES function is not supported by Postgres in the XMLELEMENT function. Is there any way to get this to work? I’ve looked at the WITH syntax but it doesn’t look like that will be helpful here.

 

Regards,

Garfield

Thx @Pavel Stehule, I’ll see if I can figure this out…  ☺

 

Regards,

Garfield

 

From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Friday, September 24, 2021 at 11:33 PM
To: Garfield Lewis <garfield.lewis@lzlabs.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Using XMLNAMESPACES with XMLEMENT

 

Hi

 

pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis <garfield.lewis@lzlabs.com> napsal:

Hi All,

 

I am attempting to port the following statement from DB2z to Postgres:

 

SELECT e.empno, e.firstnme, e.lastname,

          XMLELEMENT ( NAME "foo:Emp",

            XMLNAMESPACES('http://www.foo.com' AS "foo"),

            XMLATTRIBUTES(e.empno as "serial"),

                          e.firstnme,

                          e.lastname ) AS "Result"

       FROM EMP e

       WHERE e.edlevel = 12;

 

The NAMESPACES function is not supported by Postgres in the XMLELEMENT function. Is there any way to get this to work? I’ve looked at the WITH syntax but it doesn’t look like that will be helpful here.

 

I am afraid this is not supported in Postgres. The XMLNAMESPACE clause can be used only in XMLTABLE function. You need to make XML and in the next step you need to modify it as string with string operation.

 

It can be an interesting feature, and if it is supported by libxml2, then it can be easily implemented. But at this moment it is unsupported, and you have to use string operations - it should not be hard to use regexp.

 

Regards

 

Pavel

 

 

 

 

Regards,

Garfield