oracle 如何在oracle中提取或更新xml属性值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30151325/
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 extract or Update xml attribute values in oracle
提问by vogash
Table name: conn_details I have this xml which stored in CLOB column(conn_param) in oracle.
表名：conn_details 我有这个 xml，它存储在 oracle 的 CLOB 列（conn_param）中。
<Parameters>
<Parameter Name="USER" Value="my_user"/>
<Parameter Name="PASSWORD" Value="my_password"/>
<Parameter Name="HOST" Value="google.com"/>
</Parameters>
I am looking for the simple SQL queries which will get value or will update it.
我正在寻找将获得价值或将更新它的简单 SQL 查询。
For example: I would like to get a HOST value and the result will be: google.com I would like to update attribute PASSWORD with value my_new_password and the result will be:
例如：我想获得一个 HOST 值，结果将是：google.com 我想用值 my_new_password 更新属性 PASSWORD，结果将是：
<Parameters>
<Parameter Name="USER" Value="my_user"/>
<Parameter Name="PASSWORD" Value="my_new_password"/>
<Parameter Name="HOST" Value="google.com"/>
</Parameters>
So, I am looking something simple that I will provide some kind XPath in order to achieve my needs.
所以，我正在寻找一些简单的东西，我将提供某种 XPath 来满足我的需求。
回答by vogash
To get relevant value use this SQL:
要获取相关值，请使用此 SQL：
SELECT extract(xmltype(conn_param), '/Parameters/Parameter[@Name="HOST"]/@Value')
FROM conn_details;
To update xml value use this SQL:
要更新 xml 值，请使用以下 SQL：
UPDATE conn_details SET conn_param = UPDATEXML(xmltype(conn_param),
'/Parameters/Parameter[@Name="HOST"]/@Value',to_char('google.com')).getClobVal()
回答by Wernfried Domscheit
First you have to convert the CLOBinto an XMLType, for example like this:
首先，您必须将 转换CLOB为XMLType，例如像这样：
create table t (p CLOB) STORE P as ....;
insert into t values ('<Parameters>
<Parameter Name="USER" Value="my_user"/>
<Parameter Name="PASSWORD" Value="my_password"/>
<Parameter Name="HOST" Value="google.com"/>
</Parameters>');
insert into t values ('<Parameters>
<Parameter Name="USER" Value="my_user_2"/>
<Parameter Name="PASSWORD" Value="my_password_2"/>
<Parameter Name="HOST" Value="google.com_2"/>
</Parameters>');
create view v as
select xmlparse(DOCUMENT p WELLFORMED) as XML_doc
from t;
Then you can select it like this:
然后你可以像这样选择它：
create view xml_scalar as
SELECT
user_value, password_value, host_value, XML_doc
FROM xml_view natural join
XMLTABLE('/Parameters'
PASSING xml_view.XML_doc
COLUMNS
user_value varchar2(60) PATH 'Parameter[@Name="USER"]/@Value',
password_value varchar2(60) PATH 'Parameter[@Name="PASSWORD"]/@Value',
host_value varchar2(60) PATH 'Parameter[@Name="HOST"]/@Value')
x;
select user_value, password_value, host_value from xml_scalar;
| USER_VALUE | PASSWORD_VALUE | HOST_VALUE |
|------------|----------------|--------------|
| my_user | my_password | google.com |
| my_user_2 | my_password_2 | google.com_2 |
Then you should be able to make updates like this:
然后你应该能够像这样进行更新：
UPDATE xml_scalar
SET XML_doc =
UPDATEXML(XML_doc,
'Parameters/Parameter[@Name="PASSWORD"]/@Value', 'secret')
where user_value = 'my_user';
