[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Using XMLNAMESPACES with XMLEMENT
From: Pavel Stehule <pavel.stehule () gmail ! com>
Date: 2021-09-27 6:46:44
Message-ID: CAFj8pRDf8r5kjmyT-m77G3ju3gwPg70WPoD3V8WW7dYSH4SDdg () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (text/plain)]
Hi
ne 26. 9. 2021 v 21:48 odesílatel Garfield Lewis <garfield.lewis@lzlabs.com>
napsal:
> Thx @Pavel Stehule <pavel.stehule@gmail.com>, 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.
>
libxml2 supports it - there is an function xmlTextWriterStartElementNS
Postgres supports only the most old version of standard in this area based
on ANSI/SQL 2003. This feature was implemented later, maybe in 2006 or
2008. Postgres cannot support more modern standards because used library
libxml2 doesn't support XQuery, and there is not any other free (with BSD
licence C library). But this feature can be supported, and can be
interesting. The implementation probably cannot be trivial, because SQL
feature cannot be mapped 1:1 to libxml2 feature, but it is implementable.
I found a workaround - you can use xmlattribute instead - it is same like
in Oracle
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Oracle-Compliance-with-SQLXML2011.html#GUID-0D0F19C8-0FB7-4FDD-A55B-18839F340E17
(X080, Namespaces in XML publishing).
postgres=# select xmlelement(name "foo:Emp", XMLATTRIBUTES('
http://www.foo.com' as "xmlns:foo"), xmlelement(name "foo:name", 'Pavel'));
┌─────────────────────────── \
──────────────────────────────────────────────────┐
│ xmlelement
│
╞═══════════════════════════ \
══════════════════════════════════════════════════╡
│ <foo:Emp xmlns:foo="http://www.foo.com"><foo:name>Pavel</foo:name></foo:Emp>
│
└─────────────────────────── \
──────────────────────────────────────────────────┘
(1 row)
>
>
> Regards
>
>
>
> Pavel
>
>
>
>
>
>
>
>
>
> Regards,
>
> Garfield
>
>
[Attachment #3 (text/html)]
<div dir="ltr"><div>Hi<br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">ne 26. 9. 2021 v 21:48 odesílatel Garfield Lewis <<a \
href="mailto:garfield.lewis@lzlabs.com">garfield.lewis@lzlabs.com</a>> \
napsal:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div style="overflow-wrap: break-word;" lang="EN-CA">
<div class="gmail-m_-1753174987687289118WordSection1">
<p class="MsoNormal"><span lang="EN-US">Thx <a \
id="gmail-m_-1753174987687289118OWAAM3E3606C065FD6F40B55BCD2270318E1A" \
href="mailto:pavel.stehule@gmail.com" target="_blank"> <span \
style="font-family:"Calibri",sans-serif;text-decoration:none">@Pavel \
Stehule</span></a>, I'll see if I can figure this out… </span><span \
style="font-family:"Apple Color Emoji"" lang="EN-US">☺</span><span \
lang="EN-US"></span></p> <p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">Regards,</span></p>
<p class="MsoNormal"><span lang="EN-US">Garfield</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<div style="border-color:rgb(181,196,223) currentcolor \
currentcolor;border-style:solid none none;border-width:1pt medium medium;padding:3pt \
0cm 0cm"> <p class="MsoNormal"><b><span style="font-size:12pt;color:black">From: \
</span></b><span style="font-size:12pt;color:black">Pavel Stehule <<a \
href="mailto:pavel.stehule@gmail.com" \
target="_blank">pavel.stehule@gmail.com</a>><br> <b>Date: </b>Friday, September \
24, 2021 at 11:33 PM<br> <b>To: </b>Garfield Lewis <<a \
href="mailto:garfield.lewis@lzlabs.com" \
target="_blank">garfield.lewis@lzlabs.com</a>><br> <b>Cc: </b>"<a \
href="mailto:pgsql-general@postgresql.org" \
target="_blank">pgsql-general@postgresql.org</a>" <<a \
href="mailto:pgsql-general@postgresql.org" \
target="_blank">pgsql-general@postgresql.org</a>><br> <b>Subject: </b>Re: Using \
XMLNAMESPACES with XMLEMENT</span></p> </div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<div>
<p class="MsoNormal">Hi</p>
</div>
<p class="MsoNormal"> </p>
<div>
<div>
<p class="MsoNormal">pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis <<a \
href="mailto:garfield.lewis@lzlabs.com" \
target="_blank">garfield.lewis@lzlabs.com</a>> napsal:</p> </div>
<blockquote style="border-color:currentcolor currentcolor currentcolor \
rgb(204,204,204);border-style:none none none solid;border-width:medium medium medium \
1pt;padding:0cm 0cm 0cm 6pt;margin-left:4.8pt;margin-right:0cm"> <div>
<div>
<p class="MsoNormal"><span lang="EN-US">Hi All,</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">I am attempting to port the following \
statement from DB2z to Postgres:</span></p> <p class="MsoNormal"><span lang="EN-US"> \
</span></p> <p class="gmail-m_-1753174987687289118gmail-m-6036015254726580194p1"><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s1">SELECT</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black"> e.empno, e.firstnme, e.lastname,</span></span></p> <p \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194p1"><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194apple-converted-space"><span \
style="color:black"> </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s3">XMLELEMENT</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black"> ( NAME </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">"</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s5">foo:Emp</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">"</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black">,</span></span></p> <p \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194p1"><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black"> </span></span><span \
style="color:black">XMLNAMESPACES(</span><span \
style="color:rgb(192,5,193)">'</span><span style="color:rgb(172,31,22)"><a \
href="http://www.foo.com" target="_blank">http://www.foo.com</a></span><span \
style="color:rgb(192,5,193)">'</span><span style="color:black"> </span><span \
style="color:rgb(192,5,193)">AS</span><span style="color:black"> </span><span \
style="color:rgb(192,5,193)">"</span><span \
style="color:rgb(172,31,22)">foo</span><span \
style="color:rgb(192,5,193)">"</span><span style="color:black">)</span><span \
style="color:rgb(172,31,22)">,</span></p> <p \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194p1"><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194apple-converted-space"><span \
style="color:black"> </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black">XMLATTRIBUTES(e.empno </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">as</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black"> </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">"</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s5">serial</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">"</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black">),</span></span></p> <p \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194p1"><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194apple-converted-space"><span \
style="color:black"> </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black">e.firstnme,</span></span></p> <p \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194p1"><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194apple-converted-space"><span \
style="color:black"> </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black">e.lastname ) </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">AS</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black"> </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">"</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s5">Result</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">"</span></p> \
<p class="gmail-m_-1753174987687289118gmail-m-6036015254726580194p1"><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194apple-converted-space"><span \
style="color:black"> </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">FROM</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black"> EMP e</span></span></p> <p \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194p1"><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194apple-converted-space"><span \
style="color:black"> </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s4">WHERE</span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s2"><span \
style="color:black"> e.edlevel = </span></span><span \
class="gmail-m_-1753174987687289118gmail-m-6036015254726580194s5">12;</span></p> <p \
class="MsoNormal"><span lang="EN-US"> </span></p> <p class="MsoNormal"><span \
lang="EN-US">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.</span></p>
</div>
</div>
</blockquote>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">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.</p> </div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">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.</p></div></div></div></div></div></blockquote><div><br></div>libxml2 supports \
it - there is an function xmlTextWriterStartElementNS <br></div><div \
class="gmail_quote"><br></div><div class="gmail_quote">Postgres supports only the \
most old version of standard in this area based on ANSI/SQL 2003. This feature was \
implemented later, maybe in 2006 or 2008. Postgres cannot support more modern \
standards because used library libxml2 doesn't support XQuery, and there is not \
any other free (with BSD licence C library). But this feature can be supported, and \
can be interesting. The implementation probably cannot be trivial, because SQL \
feature cannot be mapped 1:1 to libxml2 feature, but it is implementable.</div><div \
class="gmail_quote"><br></div><div class="gmail_quote">I found a workaround - you can \
use xmlattribute instead - it is same like in Oracle <a \
href="https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Oracle-Compl \
iance-with-SQLXML2011.html#GUID-0D0F19C8-0FB7-4FDD-A55B-18839F340E17">https://docs.ora \
cle.com/en/database/oracle/oracle-database/21/sqlrf/Oracle-Compliance-with-SQLXML2011.html#GUID-0D0F19C8-0FB7-4FDD-A55B-18839F340E17</a> \
(X080, Namespaces in XML publishing).</div><div class="gmail_quote"><br></div><div \
class="gmail_quote"><span style="font-family:monospace">postgres=# select \
xmlelement(name "foo:Emp", XMLATTRIBUTES('<a \
href="http://www.foo.com">http://www.foo.com</a>' as "xmlns:foo"), \
xmlelement(name "foo:name", \
'Pavel'));<br>┌──────────────────── \
──────────────────────────── \
─────────────────────────────┐<br>│ \
xmlelement \
│<br>╞═════════════════════════ \
════════════════════════════ \
════════════════════════╡<br>│ \
<foo:Emp xmlns:foo="<a \
href="http://www.foo.com">http://www.foo.com</a>"><foo:name>Pavel</foo:name></foo:Emp> \
│<br>└───────────────────────── \
──────────────────────────── \
────────────────────────┘<br>(1 \
row)</span></div><div class="gmail_quote"><br></div><div \
class="gmail_quote"><br></div><div class="gmail_quote"><br></div><div \
class="gmail_quote"><br></div><div class="gmail_quote"><br></div><div \
class="gmail_quote"><div> </div><blockquote class="gmail_quote" style="margin:0px \
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
style="overflow-wrap: break-word;" lang="EN-CA"><div \
class="gmail-m_-1753174987687289118WordSection1"><div><div><div><p \
class="MsoNormal"><u></u><u></u></p> </div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Regards<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Pavel<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<blockquote style="border-color:currentcolor currentcolor currentcolor \
rgb(204,204,204);border-style:none none none solid;border-width:medium medium medium \
1pt;padding:0cm 0cm 0cm 6pt;margin-left:4.8pt;margin-right:0cm"> <div>
<div>
<p class="MsoNormal"><span lang="EN-US"> </span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US">Regards,</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US">Garfield</span><u></u><u></u></p>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</div>
</blockquote></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic