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
<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_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?
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
value is declared with the data type
xml (which it should be). If it’s not the case, you need to cast it: