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 100+ 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 we 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)

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 to Explore

SQL

The Oracle documentation contains a complete SQL reference.