Extracting Value from XML
Hi All,
We have a status file that is being sent in below format and we have to load the data in to a table and then extract the value.
Created a table and inserted the XML But when trying to extract value from below ,nothing is being retrieved.
SELECT EXTRACTVALUE(DATA_XML,'Documents/Document/Fields/Field/Name/Value')DOCUMENTNUMBER
FROM XMLTEST;
Please suggest
create table XmlTest( id number , data_xml XMLType) XmlType data_xml STORE AS CLOB; insert into XmlTest(id,data_xml) values(1,XMLType('<?xml version="1.0" encoding="UTF-8"?><EdiwinRetorno Reference="" Date="2018-12-04T00:00:00.000+01:00" Source="" Target=""> <Documents> <Document> <Fields> <Field> <Name>Document Number</Name> <Value>6556975</Value> </Field> <Field> <Name>Document Status</Name> <Value>ERROR</Value> </Field> <Field> <Name>Reason</Name> <Value>Missing element "TotalAmount"</Value> </Field> <Field> <Name>STATUS_TYPE</Name> <Value>EDICOM REJECTION</Value> </Field> <Field> <Name>Issue Date</Name> <Value>2018-10-18 23:59:56</Value> </Field> <Field> <Name>Country</Name> <Value>Finland</Value> </Field> </Fields> </Document> </Documents></EdiwinRetorno>'));