How to convert generic XML to a table row in PostgreSQL?

PostgresSQL v12.5

There is a table with single column containing strings formatted as XML.

create table XMLDATA (value text);

--------
  text 
--------
<something>  <a>uyt</a>  <b>xyz</b>  </something>
<something>  <a>ryu</a>  <b>sdg</b>  </something>

For simplicity let’s claim that there are no nesting: all tags inside <something> contain primitive values (strings).

Assuming that there are much more elements than <a> and <b> inside, it would be great to have an option to convert these values into a relational form without enumerating all of the nested tags manually.

Was trying to get something in documentation related to XPATH, XMLTABLE, XPATH_TABLE, but there are small number of examples that did not help me to reveal the full power of these functions.

What I am looking for is a function special_function with results like

select * from special_function(XMLDATA);

  a  |  b 
-----------
 uyt | xyz
 ryu | sdg

Could you help me find a functionality of PostgreSQL that automatically recognizes XML tags and convert their content into columns?

Answer

without enumerating all of the nested tags manually.

That’s not possible.

One fundamental restriction of SQL is, that the number, data types and names of all columns need to be known to the database before the query starts executing. SQL can’t do this “at runtime” and change structure of the query based on data that is retrieved.

---

You can extract the content using xmltable() – but as explained, there is no way without specifying each output column.

select x.*
from xmldata d
  cross join xmltable('/something' passing d.value
                       columns a  text path 'a', 
                               b  text path 'b') as x

This assumes value is declared with the data type xml (which it should be). If it’s not the case, you need to cast it: passing d.value::xml