XML Oracle: Extract specific attribute from multiple repeating child nodes
Solution 1
Aaand I managed to find the solution, which is quite simple, just added [text()="Form3"]/.../" to predicate the Xpath as in
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:
P_FORM := r.form;
P_LOYALTY_VALUE := r.value;
Solution 2
with this sql you should get the desired value:
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');
Solution 3
To extract the value you're looking for you can use the following XPATH expression:
/Customer/Loyalty/Client/Identifications/Identification/Form[text()='Form3']/../value
Then you can use XMLTABLE function to get the result
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'
)
Solution 4
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" )
mrbTT
Requirements Analyst, knowledge of SQL(PLSQL, SQL-Server and PostgreSQL) and starting on python
Updated on June 15, 2022Comments
-
mrbTT almost 2 years
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.
The response 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".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).
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