I have xml that is returned from an Oracle atom feed and I'm storing it temporarily in an apex_collection named 'ATOM'.
The XML looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<feed xmlns="http://www.w3.org/2005/Atom">
<id>atomserver:newhire:feed</id>
<title type="text">New Hire</title>
<link href="https://ucf1-fap1273-hcm.oracledemos.com/hcmCoreApi/atomservlet/employee/newhire" rel="self" />
<updated>2013-12-16T18:17:56.108Z</updated>
<entry>
<link href="https://ucf1-fap1273-hcm.oracledemos.com/hcmCoreApi/atomservlet/employee/newhire/EMP300000133185858" rel="edit" />
<id>atomservlet:newhire:EMP300000133185858</id>
<title type="text">Ford, Laura Hired</title>
<updated>2016-10-20T12:45:03.000Z</updated>
<author>
<name>BETTY.ANDERSON</name>
</author>
<summary type="text">Employee Period of Service Created</summary>
<published>2016-10-20T12:45:03.000Z</published>
<link href="https://ucf5-fap0357-hcm.oracledemos.com:443/hcmCoreApi/resources/latest/emps?q=PersonId=300000133184715&effectiveDate=2015-01-01" rel="related" />
<content type="text">{ "Context" : [ { "PrimaryPhoneNumber" : "44 1 781895862", "PersonId" : "300000133184715", "PersonName" : "Ford, Laura", "EffectiveStartDate" : "2015-01-01", "EffectiveDate" : "2015-01-01", "WorkerType" : "EMP", "PeriodType" : "E", "PersonNumber" : "3686", "WorkEmail" : "[email protected]" } ] }</content>
</entry>
</feed>
So far my SQL query is like this and it works fine:
with t as (select clob001 from apex_collections where collection_name = 'ATOM')
SELECT title, summary, content FROM t,
XMLTable( XMLNamespaces(
'http://www.w3.org/2005/Atom' AS "ns0"
), 'ns0:feed/ns0:entry'
PASSING XMLTYPE.createXML(t.CLOB001)
COLUMNS title VARCHAR2(4000) path 'ns0:title' ,
summary VARCHAR2(240) path 'ns0:summary',
content VARCHAR2(4000) path 'ns0:content');
but I need to get at the info that is inside the content tag which appears as though its JSON. I know how to parse JSON with SQL but I don't know how to parse JSON if its inside of an XML document.
Oracle's JSON dot-notation (e.g. content.Context[0].WorkEmail
) is pretty strict. It's not working here because (a) you didn't put the table alias at the beginning (your XMLTable doesn't even have a table alias), but (b) more importantly, your content
column is of type VARCHAR2(4000)
, not JSON
, and it doesn't have an IS_JSON
check constraint. I don't think you can even add a constraint to an XMLTable. All of these things are required to use dot-notation.
Based on your comment, a simpler option would be to use the JSON functions, e.g.
with t as (select clob001 from apex_collections where collection_name = 'ATOM')
SELECT title, summary,
json_value(content, '$.Context[0].WorkEmail') as work_email
FROM t,
XMLTable( XMLNamespaces(
'http://www.w3.org/2005/Atom' AS "ns0"
), 'ns0:feed/ns0:entry'
PASSING XMLTYPE.createXML(t.CLOB001)
COLUMNS title VARCHAR2(4000) path 'ns0:title' ,
summary VARCHAR2(240) path 'ns0:summary',
content VARCHAR2(4000) path 'ns0:content');