Read XML Data Value using SQL Query

DHARMEDRA SAHU Staff replied 6 years ago

This is a very common situation where the user wants to read the contents of the XML file which store in the column with Data Type as CLOB.
This SQL is very specific to below-given XML and will vary for another type of XML.

XML Example:

XYZ
XYZ

ABC

amount
STRING
650.21

firstName
STRING
Dharmendra

lastName
STRING
Sahu

………

Suppose above XML is inserted into DATA column of SampleXML table.
SQL to fetch records:

SELECT extractvalue(column_value, ‘/FIELD/FNAME’) “FNAME”,
extractvalue(column_value, ‘/FIELD/VALUE’) “VALUE” FROM TABLE( XMLSequence(XMLTYPE((SELECT XMLTYPE(DATA).EXTRACT(‘/DOCUMENT’).getStringVal() FROM SAMPLEDATA )).extract(‘/DOCUMENT/DATA/STRUCT/FIELD’))) t;l

The above XML retrieve the data from the XML. The user can update the SQL as per their requirement.