PL/SQL (MOSC)

MOSC Banner

Extracting Value from XML

edited Feb 11, 2019 3:06PM in PL/SQL (MOSC) 15 commentsAnswered ✓

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>'));

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center