Search code examples
sqlxmloraclexmltableoracle-apex-5.1

Parsing JSON inside of XML with Oracle SQL

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&amp;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');

image of result

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.

Solution

  • 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');