Oracle XMLTable-从父节点获取列

2021-10-02 00:00:00 xpath oracle xmltype xmltable oracle-xml-db

我有以下 XML 结构:

I have the following XML structure:

<root>
    <parent>
         <parent_id>1</parent_id>
         <parent_value>10000</parent_value>
         <child>
              <child_id>11</child_id>
              <other_value>1000</other_value>
         </child>
         <child>
              <child_id>12</child_id>
              <other_value>1000</other_value>
         </child>
    </parent>
</root>

预期输出:

  CHILD_ID PARENT_VALUE
---------- ------------
        11 10000            
        12 10000

我尝试过的:

WITH xtbl AS (SELECT xmltype ('<root>
                    <parent>
                         <parent_id>1</parent_id>
                         <parent_value>10000</parent_value>
                         <child>
                              <child_id>11</child_id>
                              <other_value>1000</other_value>
                         </child>
                         <child>
                              <child_id>12</child_id>
                              <other_value>1000</other_value>
                         </child>
                    </parent>
                </root>') AS xcol FROM dual)
      SELECT myXmlTable.*
        FROM xtbl
             CROSS JOIN
             xmltable ('/root/parent/child'
                       PASSING xcol
                       COLUMNS child_id NUMBER (5) PATH 'child_id',
                               parent_value NUMBER (10) PATH './parent_value') myXmlTable;

我的查询的问题是 parent_value 为空.请帮忙.

Problem with my query is that parent_value comes to be null. Please help.

推荐答案

您正在寻找./parent_node，它是一个 下 当前 节点.而那是不存在的.

You are looking for ./parent_node, which is a <parent_node> under the current <child> node. And that doesn't exist.

你只需要提升一个层次:

You just need to go up a level:

parent_value NUMBER (10) PATH './../parent_value'

使用您的 CTE 进行演示并添加 ../:

Demo with your CTE and just that added ../:

WITH xtbl AS (SELECT xmltype ('<root>
                    <parent>
                         <parent_id>1</parent_id>
                         <parent_value>10000</parent_value>
                         <child>
                              <child_id>11</child_id>
                              <other_value>1000</other_value>
                         </child>
                         <child>
                              <child_id>12</child_id>
                              <other_value>1000</other_value>
                         </child>
                    </parent>
                </root>') AS xcol FROM dual)
      SELECT myXmlTable.*
        FROM xtbl
             CROSS JOIN
             xmltable ('/root/parent/child'
                       PASSING xcol
                       COLUMNS child_id NUMBER (5) PATH 'child_id',
                               parent_value NUMBER (10) PATH './../parent_value') myXmlTable;

  CHILD_ID PARENT_VALUE
---------- ------------
        11        10000
        12        10000

相关文章