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.