Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Praveen.

Asked: November 03, 2020 - 10:43 am UTC

Last updated: November 05, 2020 - 10:19 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi,

I'm receiving the data from 3rd Party vendor through API, Which Data format(XML/JSON) is very easy to read and insert into DB table??

I have to extract the value from (XML/JSON) using PLSQL.

which one is Best to extract the value and which one is best performance??
and also suggest the function name which function is best??

Regards,
Praveen

and Chris said...

Oracle Database has extensive support for both XML and JSON, you can find details of these in the docs:

https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/index.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/index.html

which one is Best to extract the value and which one is best performance??

You can extract values from JSON using JSON_value, JSON_table, simple dot-notation, and get methods on the PL/SQL JSON object types.

You can get values from XML with XMLQuery and XMLTable.

Which of these is "best" really depends on what you're trying to do. An example of the XML or JSON you're working with would help a lot here.

and also suggest the function name which function is best??

I've no idea which function name you're referring to.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

My 2 cents

Stew Ashton, November 05, 2020 - 10:04 am UTC

I've worked with XML and studied the JSON stuff.

- The JSON stuff has better performance overall.

- What you probably need most as a "function" is either XMLTABLE or JSON_TABLE. One advantage of JSON_TABLE is that it supports the NESTED PATH syntax, which is easier to understand and get right than the XMLTABLE equivalent.

- If you have to do complicated transformations, XML allows you to use the XQUERY language. There is no equivalent in JSON.

Personally, I would be tempted to code both solutions as a learning exercise and choose afterwards. If you don't have time for that, and since you are lucky enough to be on 19c, go with JSON.

Best regards,
Stew Ashton
Chris Saxon
November 05, 2020 - 10:19 am UTC

Thanks for your thoughts Stew

Praveen, November 05, 2020 - 10:54 am UTC

Thank you so much Stev and Chris
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.