oracle 10g 如何从 XML 类型列中选择值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32837957/
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 select values from XML type column in oracle 10g
提问by user3495160
Below is my xml column with name xml_col. I want to select all plan names where ProductType is productname and ProductCode is 1111.
下面是我的名为 xml_col 的 xml 列。我想选择 ProductType 为 productname 且 ProductCode 为 1111 的所有计划名称。
<life>
<Holding id="Holding">
<HoldingTypeCode tc="2">Policy</HoldingTypeCode>
<Purpose tc="11">example</Purpose>
<Policy>
<ProductType tc="235684546454">productname</ProductType>
<ProductCode>1111</ProductCode>
<PlanName>name_plan</PlanName>
</Policy>
</Holding>
</life>
I tried the below simple to select, its not working for me. please help
我尝试了以下简单的选择，但对我不起作用。请帮忙
select extractValue(xml_col,'/LifE/Policy/PlanName') "plan"
FROM mytable
to query mt scenario, i have queried as below which shows an error "ORA-00904: "schemaname"."tablename "."xml_col": invalid identifier" pls help
要查询 mt 场景，我查询如下，显示错误“ORA-00904：“schemaname”。“tablename”。“xml_col”：无效标识符“请帮助
select extractValue(xml_col,'/lifE/Holding/Policy/PlanName') "plan name" FROM schemaname.tablename WHERE schemaname.tablename.xml_col.exist('/lifE/Holding/Policy/ProductCode[text() = "1111"]') = 1
回答by some1
@mikron is correct. XML is case sensitive.
@mikron 是正确的。XML 区分大小写。
select extractValue(xml_col,'/life/Holding/Policy/PlanName') "plan"
FROM mytable
EDIT: updated as pointed out by @mikron
编辑：如@mikron 指出的那样更新
回答by Boneist
If I were you, I wouldn't use extract or extractvalue, since they're both deprecated since Oracle 10g. Instead, use XMLTABLE, something like:
如果我是你，我不会使用提取或提取值，因为它们都从 Oracle 10g 开始被弃用。相反，使用 XMLTABLE，例如：
with sample_data as (select xmltype(
'<life>
<Holding id="Holding">
<HoldingTypeCode tc="2">Policy</HoldingTypeCode>
<Purpose tc="11">example</Purpose>
<Policy>
<ProductType tc="235684546454">productname</ProductType>
<ProductCode>1111</ProductCode>
<PlanName>name_plan</PlanName>
</Policy>
</Holding>
<Holding id="Holding">
<HoldingTypeCode tc="2">Policy</HoldingTypeCode>
<Purpose tc="11">example</Purpose>
<Policy>
<ProductType tc="235684546454">productname</ProductType>
<ProductCode>1112</ProductCode>
<PlanName>name_plan</PlanName>
</Policy>
</Holding>
</life>') xml_data from dual)
select xml_data,
x.*
from sample_data sd
cross join xmltable('/life/Holding'
passing sd.xml_data
columns product_type varchar2(20) path 'Policy/ProductType',
product_code number path 'Policy/ProductCode',
plan_name varchar2(20) path 'Policy/PlanName') x
where x.product_type = 'productname'
and x.product_code = 1111;
XML_DATA PRODUCT_TYPE PRODUCT_CODE PLAN_NAME
---------- ------------ ------------ ---------
<life>... productname 1111 name_plan
