Is there a good XML-to-Table that can be used in Java (or PostgreSQL)?

Question

I’m looking for something exactly like: XMLTABLE, http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/

Does something like this exist in PostgreSQL, or what would be closest?

Or another way, is there a Java library that can accomplish this?

EDIT:

Thanks to Erwin (the answer in his comment is almost precisely what I was looking for).

However, perhaps I could suggest an extension to this.

Consider, we have an xml document like:

<comments photo_id=“123”>
    </comment>this is the first comment</comment>
    </comment>this is the second comment</comment>
</comments>

While, this is a simple example, consider also that “comment” could be quite sophisticated.

My question is now: using the XMLTable function (or Erwin’s implementation), we need to specify a path_to_data i.e. in this case (/comment).

However, if I want my return schema to be something like: [photo_id, comment_text].

There is no way to get data from the elements of the parent of the datanum.

Is it therefore possible to somehow modify your code to do this? My guess is having something more sophisticated than the xpath function, which essentially returns a subset of data by tracing to the parent.

For example:

<comments photo_id=“123”>
    </comment>this is the first comment</comment>
</comments>

<comments photo_id=“123”>
    </comment>this is the second comment</comment>
</comments>

In this case, we can access “/comments/@photo_id”.

I posted a complete code example in plpgsql for importing data from XML files to PostgreSQL tables [in this answer](http://stackoverflow.com/a/7628453/939860) to a related question. — Erwin Brandstetter, Feb 19 '12 at 17:34
@ErwinBrandstetter: thanks so much, this is almost just what I needed. I have edited my question further, do you think you could modify your suggested implementation to handle such a case? Please also then post this as an answer, so that I can accept. Thanks again! — Larry, Feb 20 '12 at 12:29
My work schedule is tight atm. I'll have a look at the follow up question, when I can. — Erwin Brandstetter, Feb 20 '12 at 14:41
@ErwinBrandstetter: thanks, if you could hopefully try to manage this soon, it would be much appreciated. As I really need it for a project. — Larry, Feb 20 '12 at 16:19

score 2 · Accepted Answer · edited May 23 '17 at 12:09

I finally got some time to take a closer look. From what I gather in your example this might be what you are looking for:

Test setup:

I added another node to make my point clear:

-- DROP TABLE t;
CREATE TEMP TABLE t (x xml);
INSERT INTO t VALUES (
'<tbl>
<comments photo_id="123">
     <comment>this is the first 123 comment</comment>
     <comment>this is the second 123 comment</comment>
</comments>
<comments photo_id="124">
     <comment>this is the first 124 comment</comment>
     <comment>this is the second 124 comment</comment>
     <comment>this is the third 124 comment</comment>
</comments>
</tbl>'::xml);

Query:

SELECT (xpath('./@photo_id', c.node))[1] AS photo_id
     , unnest(xpath('./comment/text()', c.node)) AS descriptor
FROM  (             
    SELECT unnest(xpath('./comments', x)) AS node
    FROM   t
    ) c;

Result:

 photo_id |           descriptor
----------+--------------------------------
 123      | this is the first 123 comment
 123      | this is the second 123 comment
 124      | this is the first 124 comment
 124      | this is the second 124 comment
 124      | this is the third 124 comment

The result looks very simple, but it caused me quite some headache to get there (a while ago, actually).

Key ingredients are the functions xpath() and unnest(). The trick is to do it in two steps. You can find some more explanation at this related answer.

score 0 · Answer 2 · answered Feb 19 '12 at 16:33

PostgreSQL does have support for XML datatypes, but no direct support for XML-to-table conversion. You could write a XSLT stylesheet to convert your XML into SQL INSERT statements, or maybe look at a mapping tool such as Castor, which can convert between XML, Java beans and SQL.

Is there a good XML-to-Table that can be used in Java (or PostgreSQL)?

2 Answers2

Test setup:

Query:

Result:

Linked