如何从 Oracle 表中检索特定的 XML 节点？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3863162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to retrieve a specific XML node from an Oracle table?
提问by Pradeep
I have an oracle table which has a column that stores XML in a CLOB format. The XML has the following syntax:
我有一个 oracle 表，它有一列以 CLOB 格式存储 XML。XML 具有以下语法：
<?xml version="1.0" encoding="ISO-8859-1" ?>
<BaseXML Version="2009.4">
<InvoiceCanvasDetails>
<Grouping>
<ParentGroup Name=”Parent group 1” ID=”100”>
<ParentGroupLineItems>
<Item ID="461616" Name=”Item 1”>
<Papers Quantity=10000>
<Paper Name="UNCOATED GROUNDWOOD SCA+ (25X38)"
ID="126287" Weight="1268" Type=”A4” />
<Paper Name="COATED GROUNDWOOD SCA+ (25X38)"
ID="126288" Weight="1290" Type=”A4” />
</Papers>
</Item>
</ParentGroupLineItems>
</ParentGroup>
</Grouping>
</InvoiceCanvasDetails>
</BaseXML>
Now, I want to retrieve only the Paper information corresponding to each item. ie, Given an item ID, retrieve all the papers associated to it using a query. Please guide me which is the best way to do this.
现在，我只想检索与每个项目对应的 Paper 信息。即，给定一个项目 ID，使用查询检索与其关联的所有论文。请指导我这是最好的方法。
回答by Vincent Malgrat
You can use a combination of extractand extractvalue:
您可以使用组合extract和extractvalue：
SQL> SELECT extractvalue(column_value, 'Paper/@Name') NAME,
2 extractvalue(column_value, 'Paper/@ID') ID,
3 extractvalue(column_value, 'Paper/@Weight') Weight,
4 extractvalue(column_value, 'Paper/@Type') TYPE
5 FROM TABLE (SELECT xmlsequence(XMLTYPE(a).extract('BaseXML/' ||
6 'InvoiceCanvasDetails/' ||
7 'Grouping/ParentGroup/' ||
8 'ParentGroupLineItems/' ||
9 'Item/Papers/Paper'))
10 FROM t);
NAME ID WEIGHT TYPE
---------------------------------------- ---------- ---------- -----
UNCOATED GROUNDWOOD SCA+ (25X38) 126287 1268 A4
COATED GROUNDWOOD SCA+ (25X38) 126288 1290 A4
If you're looking for a specific ID, you can filter data directly in the extractfunction:
如果要查找特定 ID，可以直接在extract函数中过滤数据：
SQL> SELECT extract(XMLTYPE(a),
2 'BaseXML/InvoiceCanvasDetails/Grouping/' ||
3 'ParentGroup/ParentGroupLineItems/' ||
4 'Item/Papers/Paper[@ID="126287"]') ext
5 FROM t;
EXT
--------------------------------------------------------------------------------
<Paper Name="UNCOATED GROUNDWOOD SCA+ (25X38)" ID="126287" Weight="1268" Type="A
4"/>
回答by Pablo Santa Cruz
Take a look at using XPATH expressionsin Oracle.
看看在 Oracle中使用XPATH 表达式。
