Postgresql – Get a Table from XML in postgresql

postgresqlxml

Have a look at the following XML data:

<data>
    <test color="red">Red text</test>
    <test color="green">green</test>
</data>

Let's say I have several xml-documents with this structure in my database:

CREATE TABLE xmldata (
    id bigserial not null,
    documents xml)

Now I want to select all possible colors:

SELECT id, xpath('//test', xml) FROM xmldata;

But this returns a table with the id of each document and a text-array of the test-nodes. Furthermore, documents without any "test" node exist in the result as well – with an empty array {}

What I really want is a table like this:

| id | node                              |
|  1 | <test color="red">Red text</test> |
|  1 | <test color="green">green</test>  |

What is the syntax I have to use?

I heard that xpath_table may be the function to use – but this function is marked as deprecated…

(The returned table has to have one line for each occurence of the node I searched for. The node itself maybe an xml-snippet, text or something else – isn't really important)

Best Answer

There is nothing wrong with using xpath_table().

Even though some of the functions in the xml2 extension have been replaced with in-core functions, xpath_table is not one of them (I think this will happen for Postgres 10).

Until then, xpath_table() seems to be your only option:

SELECT * 
FROM xpath_table('id',
            'documents',
            'xmldata',
            '//test',
            'true') AS t(doc_id integer, data text);

Returns:

doc_id | data    
-------+---------
     1 | Red text
     1 | green

In order for this to work you first need to create the extension (as a superuser):

create extension xml2;

Otherwise the function isn't available.

Related Question