XML Oracle：从多个重复的子节点中提取特定属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34925399/
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
XML Oracle: Extract specific attribute from multiple repeating child nodes
提问by mrbTT
I'm having trouble understanding other questions I see, as they are a little bit different.
我无法理解我看到的其他问题，因为它们有点不同。
I'm getting a XML as response from a webservice vi UTL_HTTP. The XML has repeating child nodes and I want to extract only 1 specific value.
我从 web 服务 vi UTL_HTTP 得到一个 XML 作为响应。XML 具有重复的子节点，我只想提取 1 个特定值。
The response XML:
响应 XML：
<Customer>
<Loyalty>
<Client>
<Identifications>
<Identification>
<Form>Form1</Form>
<value>1234</value>
</Identification>
<Identification>
<Form>Form2</Form>
<value>4442</value>
</Identification>
<Identification>
<Form>Form3</Form>
<value>9995</value>
</Identification>
</Identifications>
</Client>
</Loyalty>
</Customer>
I need to extract the the node <value>only where the node <Form>= "Form3".
我只需要<value>在节点<Form>=“Form3”的地方提取节点。
So, within my code, I receive the response from another function
所以，在我的代码中，我收到了另一个函数的响应
v_ds_xml_response XMLTYPE;
-- Here would lie the rest of the code (omitted) preparing the XML and next calling the function with it:
V_DS_XML_RESPONSE := FUNCTION_CALL_WEBSERVICE(
P_URL => V_DS_URL, --endpoint
P_DS_XML => V_DS_XML, --the request XML
P_ERROR => P_ERROR);
With that, I created a LOOP to store the values. I've tried using WHERE and even creating a type (V_IDENTIFICATION below is the type), but It didn't return anything (null).
有了这个，我创建了一个 LOOP 来存储值。我试过使用 WHERE 甚至创建一个类型（下面的 V_IDENTIFICATION 是类型），但它没有返回任何内容（空）。
for r IN (
SELECT
ExtractValue(Value(p),'/Customer/Loyalty/Client/Identifications/Identification/*[local-name()="Form"]/text()') as form,
ExtractValue(Value(p),'/Customer/Loyalty/Client/Identifications/Identification/*[local-name()="value"]/text()') as value
FROM TABLE(XMLSequence(Extract(V_DS_XML_RESPONSE,'//*[local-name()="Customer"]'))) p
LOOP
V_IDENTIFICATION.FORM := r.form;
V_IDENTIFICATION.VALUE := r.value;
END LOOP;
SELECT
V_IDENTIFICATION.VALUE
INTO
P_LOYALTY_VALUE
FROM dual
WHERE V_IDENTIFICATION.TIPO = 'Form3';
Note, P_LOYALTY_VALUE is an OUT parameter from my Procedure
注意，P_LOYALTY_VALUE 是我的程序中的一个 OUT 参数
采纳答案by mrbTT
Aaand I managed to find the solution, which is quite simple, just added [text()="Form3"]/.../" to predicate the Xpath as in
Aaand 我设法找到了解决方案，这很简单，只是添加了 [text()="Form3"]/.../" 来预测 Xpath，如
SELECT
ExtractValue(Value(p),'/Customer/Loyalty/Client/Identifications/Identification/*[local-name()="Form"][text()="Form3"]/text()') as form,
ExtractValue(Value(p),'/Customer/Loyalty/Client/Identifications/Identification/Form[text()="Form3"]/.../*[local-name()="value"]/text()') as value
Also extracted the values just sending them directly into the procedure's OUT parameter:
还提取了直接将它们发送到过程的 OUT 参数中的值：
P_FORM := r.form;
P_LOYALTY_VALUE := r.value;
回答by Frank Ockenfuss
with this sql you should get the desired value:
使用此 sql，您应该获得所需的值：
with data as
(select '<Customer>
<Loyalty>
<Client>
<Identifications>
<Identification>
<Form>Form1</Form>
<value>1234</value>
</Identification>
<Identification>
<Form>Form2</Form>
<value>4442</value>
</Identification>
<Identification>
<Form>Form3</Form>
<value>9995</value>
</Identification>
</Identifications>
</Client>
</Loyalty>
</Customer>' as xmlval
from dual b)
(SELECT t.val
FROM data d,
xmltable('/Customer/Loyalty/Client/Identifications/Identification'
PASSING xmltype(d.xmlval) COLUMNS
form VARCHAR2(254) PATH './Form',
val VARCHAR2(254) PATH './value') t
where t.form = 'Form3');
回答by Arkadiusz ?ukasiewicz
When you don't know exact path.
当您不知道确切路径时。
select * from
xmltable('for $i in $doc//*/Form
where $i = "Form2"
return $i/../value'
passing
xmltype('<Customer>
<Loyalty>
<Client>
<Identifications>
<Identification>
<Form>Form1</Form>
<value>1234</value>
</Identification>
<Identification>
<Form>Form2</Form>
<value>4442</value>
</Identification>
<Identification>
<Form>Form3</Form>
<value>9995</value>
</Identification>
</Identifications>
</Client>
</Loyalty>
</Customer>') as "doc" )
回答by Alessandro Rossi
To extract the value you're looking for you can use the following XPATH expression:
要提取您要查找的值，您可以使用以下 XPATH 表达式：
/Customer/Loyalty/Client/Identifications/Identification/Form[text()='Form3']/../value
Then you can use XMLTABLE function to get the result
然后你可以使用 XMLTABLE 函数来获取结果
SELECT my_value
FROM xmltable(
'/Customer/Loyalty/Client/Identifications/Identification/Form[text()=''Form3'']/../value'
PASSING xmltype(
'<Customer>
<Loyalty>
<Client>
<Identifications>
<Identification>
<Form>Form1</Form>
<value>1234</value>
</Identification>
<Identification>
<Form>Form2</Form>
<value>4442</value>
</Identification>
<Identification>
<Form>Form3</Form>
<value>9995</value>
</Identification>
</Identifications>
</Client>
</Loyalty>
</Customer>')
COLUMNS
my_value VARCHAR2(4000) path '/value'
)
