I am trying to extract xml from the xmltype
doc.
DECLARE
xmlData XMLType;
sDocumentId VARCHAR2(100);
sFormat VARCHAR2(100);
cData CLOB;
BEGIN
xmlData := XMLType( '<main>
<document_id>1234567</document_id>
<format>E66</format>
<data><Fname>ABCD</Fname><Lname>EFGD</Lname></data>
</main>');
SELECT ExtractValue(xmlData, '/main/document_id/text()'),
ExtractValue(xmlData, '/main/format/text()'),
ExtractValue(xmlData, '/main/data/text()')
INTO sDocumentId,
sFormat,
cData
FROM (SELECT xmlData FROM DUAL);
dbms_output.put_line(sDocumentId);
dbms_output.put_line(sFormat);
dbms_output.put_line(cData);
END;
How to get the output like this:
1234567
E66
<Fname>ABCD</Fname><Lname>EFGD</Lname>
You can do it like this:
SELECT EXTRACTVALUE(xmlData, '/main/document_id/text()'),
EXTRACTVALUE(xmlData, '/main/format/text()'),
EXTRACT(xmlData, '/main/data').GetClobVal()
INTO sDocumentId,
sFormat,
cData
FROM (SELECT xmlData FROM DUAL) t;
Please note, EXTRACTVALUE
and EXTRACT
are deprecated. However, I did not manage to use XMLTABLE
, but this could be a starting point:
SELECT *
INTO sDocumentId,
sFormat,
cData
FROM XMLTABLE('/main' PASSING xmlData COLUMNS
sDocumentId VARCHAR2(100) PATH 'document_id/text()',
sFormat VARCHAR2(100) PATH 'format/text()',
cData VARCHAR2(1000) PATH 'data/text()');