本文介紹了Oracle XMLTable-從父節(jié)點(diǎn)獲取列的處理方法，對(duì)大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
限時(shí)送ChatGPT賬號(hào)..
我有以下 XML 結(jié)構(gòu):
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>
預(yù)期輸出:
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 為空.請(qǐng)幫忙.
Problem with my query is that parent_value comes to be null. Please help.
推薦答案
您正在尋找./parent_node，它是一個(gè)
You are looking for ./parent_node, which is a <parent_node> under the current <child> node. And that doesn't exist.
你只需要提升一個(gè)層次:
You just need to go up a level:
parent_value NUMBER (10) PATH './../parent_value'
使用您的 CTE 進(jìn)行演示并添加 ../:
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
這篇關(guān)于Oracle XMLTable-從父節(jié)點(diǎn)獲取列的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！