PostgreSQL XMLTABLE with repeating paths

42 Views Asked by At

I have a case when fetching a XMLTABLE with pgsql where I want to reuse one of the columns to further narrow down the path for other columns.

SELECT xmltable.*
FROM xmlcontent,
    XMLTABLE(
            XMLNAMESPACES(
                'http://www.w3.org/1999/xhtml' AS default,
                'http://www.idpf.org/2007/ops' AS epub,
                'http://www.w3.org/1998/Math/MathML' AS m
                ),
            '//default:section[@id = "firstSection"][1]'
            PASSING content
            COLUMNS
                id varchar PATH '@id',
                header varchar PATH 'default:h1[@epub:type = "title"][1]/default:span',
                label varchar PATH '$header/default:span[@epub:type = "label"][1]/node()',
                ordinal varchar PATH '$header/default:span[@epub:type = "ordinal"][1]/node()',
                title varchar PATH '$header/default:span[@epub:type = "title"][1]/node()'
        )
WHERE xmlcontent.id = 1;

In this case I want to use the result from the header column to further get label, ordinal and title from it. Of course this produces an error because the $header variable is not declared and I wasn't able to find answers how to do that.

I also tried declaring the header column as XML type: header XML PATH 'default:h1[@epub... , but I'm not sure if I can reference that XML node in another column inside the same XMLTABLE, only if I do PASSING header inside another XMLTABLE, but it will mess up my results.

Any advice or suggestion would be appreciated. Thanks.

0

There are 0 best solutions below