Hi,
I have loaded an xml file into a column (xmlname) in a table (customer). I'm been Googling around trying to teach myself how to read back the xml data. I've come across various posts but the common theme is to convert the blob to a clob!
Is this the correct approach and if so how do you convert a blob to a clob?
Then how do you construction an sql statement to read back the xml data (for example the /ruleServiceReport/hostId)?
select * from customer
| 161 | TEST | [unsupported data type] |
| Column Name | Data Type | Nullable | Default | Primary Key |
|---|
| ID | NUMBER | No | - | 1 |
| CUSTOMERNAME | VARCHAR2(250) | Yes | - | - |
| XMLNAME | BLOB | Yes | - | - |
snippet of xml data stored:
<ruleServiceReport xmlns="http://blahblahblah">
<jobRequestId>1416140692864</jobRequestId> <hostId>
<analysisDate>
<reportType>
<bestPracticeReport xmlns:ns2="http:/blahblahblah">
<ns2:extractor/><ns2:parser/>
<ns2:ruleServiceInfo/>
<ns2:customerInfo>
<ns2:customerName>Mr Customer</ns2:customerName> <ns2:fullName/>
<ns2:userEmail/>
I'm using APEX and the following:
| BANNER |
|---|
| Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production |
| PL/SQL Release 11.2.0.4.0 - Production |
| CORE 11.2.0.4.0 Production |
| TNS for Linux: Version 11.2.0.4.0 - Production |
| NLSRTL Version 11.2.0.4.0 - Production |
Any help would be appreciated.
Jason