是否有一个很好的 XML-to-Table 可以在 Java(或 PostgreSQL)中使用? [英] Is there a good XML-to-Table that can be used in Java (or PostgreSQL)?

问题描述

我正在寻找完全类似于:XMLTABLE，http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/

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

PostgreSQL 中是否存在这样的东西，或者最接近的东西?

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

或者换一种方式，有没有可以实现这一点的 Java 库?

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

感谢 Erwin(他评论中的答案几乎正是我想要的).

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.

考虑一下，我们有一个 xml 文档，如:

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>

虽然这是一个简单的例子，但也要考虑到 comment" 可能非常复杂.

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

我现在的问题是:使用 XMLTable 函数(或 Erwin 的实现)，我们需要指定一个 path_to_data，即在这种情况下 (/comment).

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).

但是，如果我希望我的返回模式类似于:[photo_id, comment_text].

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

无法从 datanum 的父元素中获取数据.

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

因此是否有可能以某种方式修改您的代码来执行此操作?我的猜测是有一些比 xpath 函数更复杂的东西，它本质上通过跟踪到父级来返回数据的一个子集.

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.

例如:

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

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

在这种情况下，我们可以访问/comments/@photo_id".

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

推荐答案

我终于有时间仔细看看了.根据我在您的示例中收集的信息，这可能就是您要查找的内容:

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:

我添加了另一个节点来明确我的观点:

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);

查询:

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;

结果:

 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).

关键成分是函数 xpath() 和 unnest().诀窍是分两步完成.您可以在此相关答案中找到更多解释.

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.

这篇关于是否有一个很好的 XML-to-Table 可以在 Java(或 PostgreSQL)中使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！