Skip to content
Advertisement

Postgresql Convert SQL XML Coding to SQL JSON Coding

How to convert XML SQL Coding to JSON SQL Coding. Example:

SELECT XMLELEMENT(NAME "ORDER", XMLFOREST(PURCHASE_ORDER AS OD_NO)) AS "XMLELEMENT" FROM 
TBL_SALES

Now how to convert this XMLELEMENT & XMLFOREST into JSON functions. Please help me. Do we have equivalent XMLELEMENT/XMLFOREST in JSON functions.

xml:

<order><OD_NO>4524286167</OD_NO><order_date>2020-06-15</order_date><sales_office>CH</sales_office></order> 

json:

{ "OD_NO": "4524286167", "order_date": "2020-06-15", "sales_office": "CH" }

Advertisement

Answer

These Postgresql functions
json_build_object(VARIADIC "any") and
jsonb_build_object(VARIADIC "any")
are semantically close to XMLELEMENT and very convenient for ‘embroidering‘ of whatever complex JSON you may need. Your query might look like this:

select json_build_object
(
  'OD_NO', order_number, -- or whatever the name of the column is
  'order_date', order_date,
  'sales_office', sales_office
) as json_order
from tbl_sales;

I do not think that there is a XMLFOREST equivalent however.

Advertisement