Read XML Data attribute Value From the Database through SQL Query

Introduction

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 a CLOB.
This SQL is very specific to below-given XML and will vary for another type of XML.

Sample XML

The sample XML files are given below which are stored into Database table name SampleData. The Data type of the column is used as a CLOB.

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

SQL Query Example

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;

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

SQL Query Result

The SQL executed for different table and Data so it is different from the above SQL but result will display something like –

Read XML Directly

Result of above SQL Query

Alternate Example

This section will provide a sample SQL Query when stored XML is different than what we discussed in the previous paragraph. The below XML is a best example and used across many project to store data into the database in the form of XML. The user can read the value of attribute’s element using the below SQL Query.

The below SQL Query is straight forward and used to read the element’s value like accommodationStartDate, accommodationStatus etc.

The Output of the above SQL Query will be like –

Alternate Example

Suppose, the user is having different type of XML then use below SQL to extract the data from clob.

Please find the sample SQL Query in below section:

SELECT EXTRACTVALUE(xmltype(sampleclob), ‘/KWResponse/ContextData/Field[@key=”Decision”]’)
FROM sampletableclob;