Recursive CTEs are sublime, not only for orthodox trees: combined with XMLTABLE you can even use it for recursively descending XML, normalizing it into tuples o

Recursive CTEs are sublime, not only for orthodox trees: combined with XMLTABLE you can even use it for recursively descending XML, normalizing it into tuples of ([array, of, tag, names], value). Then you can join on that table/view using simple and easily composable autogenerated SQL expressions instead of Xpath. And you can string-join the element path with '/' to get an Xpath expression that would get you a value from the source XML, if you need to! I use this a lot to work with XML, even exposing it through an ORM (Django's in my case). You can do a similar recursive descend on JSON.