Re: xml build a list of all elements

On Tue, Mar 26, 2024 at 2:05 PM ft <ml(at)ft-c(dot)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='1'>comment</sub2>
> <sub2 attr='2'>comment</sub2>
> <sub>
> </area>
>
> The result should be:
> /area
> /area/sub
> /area/sub/sub2
> /area/sub/sub2(at)attr
> /area/sub/sub2
> /area/sub/sub2(at)attr
>
> or distinct (it's enough)
> /area
> /area/sub
> /area/sub/sub2
> /area/sub/sub2(at)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