MIME-Version: 1.0
References: <2c00e954c31459c43f0d78119c1bd0547f7eddb2.camel@ft-c.de>
In-Reply-To: <2c00e954c31459c43f0d78119c1bd0547f7eddb2.camel@ft-c.de>
From: Steve Midgley <science@misuse.org>
Date: Tue, 26 Mar 2024 15:27:27 -0700
Message-ID: 
 <CAJexoSLc5kimE1C83xjA9p_MLcvNyLs3Y1XjBe1M6mSpkBaoaQ@mail.gmail.com>
Subject: Re: xml build a list of all elements
To: ml@ft-c.de
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000333c7c061497cc78"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJexoSLc5kimE1C83xjA9p_MLcvNyLs3Y1XjBe1M6mSpkBaoaQ%40mail.gmail.com>
Precedence: bulk

--000000000000333c7c061497cc78
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 26, 2024 at 2:05=E2=80=AFPM ft <ml@ft-c.de> wrote:

> Hello,
>
> I have a postgresql database table with xml data.
> What I need is a list of all elements and attributes.
> Example:
> <area>
>   <sub>
>    <sub2 attr=3D'1'>comment</sub2>
>    <sub2 attr=3D'2'>comment</sub2>
>   <sub>
> </area>
>
> The result should be:
> /area
> /area/sub
> /area/sub/sub2
> /area/sub/sub2@attr
> /area/sub/sub2
> /area/sub/sub2@attr
>
> or distinct (it's enough)
> /area
> /area/sub
> /area/sub/sub2
> /area/sub/sub2@attr
>
> Is there a postgresql function to build this list?
>
>
> I think it can be done but it'll be a painful function. I'd personally
recommend writing this in a higher level language that deals with xml as
rationally as it is possible to deal with xml (which is very unfriendly to
most programming languages). Possibly you could consider using a PL/Python
extension if you want processing to happen on the server, but I have no
idea about security/resource consumption implications of that approach. But
if you can get Python's lxml installed on your Pg server, having a library
like that would (for me) make this function a lot easier to write..

Steve

--000000000000333c7c061497cc78
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Mar 26, 2024=
 at 2:05=E2=80=AFPM ft &lt;<a href=3D"mailto:ml@ft-c.de">ml@ft-c.de</a>&gt;=
 wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hello,=
=C2=A0<br>
<br>
I have a postgresql database table with xml data.=C2=A0<br>
What I need is a list of all elements and attributes.<br>
Example:<br>
&lt;area&gt;<br>
=C2=A0 &lt;sub&gt;<br>
=C2=A0 =C2=A0&lt;sub2 attr=3D&#39;1&#39;&gt;comment&lt;/sub2&gt;<br>
=C2=A0 =C2=A0&lt;sub2 attr=3D&#39;2&#39;&gt;comment&lt;/sub2&gt;<br>
=C2=A0 &lt;sub&gt;<br>
&lt;/area&gt;<br>
<br>
The result should be:<br>
/area<br>
/area/sub<br>
/area/sub/sub2<br>
/area/sub/sub2@attr<br>
/area/sub/sub2<br>
/area/sub/sub2@attr<br>
<br>
or distinct (it&#39;s enough)<br>
/area<br>
/area/sub<br>
/area/sub/sub2<br>
/area/sub/sub2@attr<br>
<br>
Is there a postgresql function to build this list?<br>
<br><br></blockquote><div>I think it can be done but it&#39;ll be a painful=
 function. I&#39;d personally recommend writing this in a higher level lang=
uage that deals with xml as rationally as it is possible to deal with xml (=
which is very unfriendly to most programming languages). Possibly you could=
 consider using a PL/Python extension if you want processing to happen on t=
he server, but I have no idea about security/resource consumption implicati=
ons of that approach. But if you can get Python&#39;s lxml installed on you=
r Pg server, having a library like that would (for me) make this function a=
 lot easier to write..</div><div><br></div><div>Steve</div></div></div></di=
v>

--000000000000333c7c061497cc78--