Parsing JSON inside of XML with Oracle SQL

Question

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

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.

I don't uderstand. At this point content has been extracted from the XML as relational data. So why can't you parse that with the method you already have? Whact happened when you tried? — Alex Poole
– Alex Poole, Commented Aug 13, 2017 at 10:05
The contents of the content tag is JSON. So if I want Title, Summary and WorkEmail I want to do something like this: with t as (select clob001 from apex_collections where collection_name = 'ATOM') SELECT title, summary, content.Context[0].WorkEmail FROM....etc etc. but that throws me ORA-00923: FROM keyword not found where expected. — John k.
– John k., Commented Aug 13, 2017 at 21:03

kfinity · Accepted Answer · 2017-08-14 14:42:13Z

1

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

answered Aug 14, 2017 at 14:42

kfinity

9,1411 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John k. Over a year ago

Thank you very much. I am marking this as correct. One of the databases I am running on is still at 11.2 so I don't have access to json_value but I neglected to mention that in my post. But this is great reference for when we finally get upgraded.

Collectives™ on Stack Overflow

Parsing JSON inside of XML with Oracle SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related