hi all good afternoon,
I have tried to write a query to extract value from XML like below query.
SELECT external_reference_number, provider_name,
account_id, provider_account_status,
status_date, extensible_data,
EXTRACTVALUE
(extensible_data,
/ProviderAccount/additionalInfoList/additionalInfo[3]/value'
) AS login_id,
EXTRACTVALUE
(extensible_data,
/ProviderAccount/additionalInfoList/additionalInfo[5]/value'
) AS pwd,
date_created, user_created, app_created,
date_modified, user_modified, app_modified,
SYSDATE + 1000 AS date_sent_to_hist
from table_name;
sample xml
----------
<provideraccount>
<lineofbusiness>applet</lineofbusiness>
-<additionalinfolist> -<additionalinfo>
<name>servicenumber</name> <value>JF</value>
</additionalinfo>
-<additionalinfo>
<name>template</name>
<value>pass</value> </additionalinfo>
<additionalinfo>
<name>LOGIN_ID</name>
<value>tpltsr789456</value>
</additionalinfo>
<additionalinfo> <name>plsdata</name>
<value>true</value> </additionalinfo>
<additionalinfo> <name>PWD</name>
<value>kjh4235pls</value> </additionalinfo>
</additionalinfolist> -<provider>
<partnerid>TT</partnerid> <name>telecom</name>
<sourceid>VODAFONE</sourceid>
</provider> -<providerserviceinfolist>
<providerserviceinfo>
<servicecode>JF</servicecode>
</providerserviceinfo> </providerserviceinfolist>
</provideraccount>
------------------------------------------------------------
the above query working fine for this XML.
in the above XML,we can identify login_id and pwd placed at 3rd and 5th locations.
now the requirement is ,sometimes login_id and pwd passes in different locations.
how can i identify the login_id and pwd locations and how to fetch both?
please provide solution to understand easily...