converting xml to json using postgresql

Question

I am working on converting XML to j son string using the PostgreSQL. we have attributecentric XML and would like to know how to convert it to j son.

Example XML:

<ROOT><INPUT id="1" name="xyz"/></ROOT>

Need to get the j son as follows:

{ "ROOT": { "INPUT": {  "id": "1", "name": "xyz" }}}

got the above json format from an online tool.

any help or guidance will be appreciated.

Regards Abdul Azeem

Answer 1

Basically, breakdown of this problem is the following:

• extract values from given XML using xpath() function
• generate and combine JSON entries using json_object_agg() function

The only tricky thing here is to combine key,value pairs together from xpath()/json_object_agg() results, which are{ "id": "1"} and { "name" : "xyz"}.

WITH test_xml(data) AS ( VALUES
  ('<ROOT><INPUT id="1" name="xyz"/></ROOT>'::XML)
), attribute_id(value) AS (
  -- get '1' value from id
  SELECT (xpath('//INPUT/@id',test_xml.data))[1] AS value 
  FROM test_xml
), attribute_name(value) AS (
  -- get 'xyz' value from name
  SELECT (xpath('//INPUT/@name',test_xml.data))[1] AS value 
  FROM test_xml
), json_1 AS (
  -- generate JSON 1 {"id": "1"}
  SELECT  json_object_agg('id',attribute_id.value) AS payload 
  FROM attribute_id
), json_2 AS (
  -- generate JSON 2 {"name" : "xyz"}
  SELECT  json_object_agg('name',attribute_name.value) AS payload FROM attribute_name
), merged AS (
  -- Generate INPUT result - Step 1 - combine JSON 1 and 2 as single key,value source
  SELECT key,value
  FROM json_1,json_each(json_1.payload)
  UNION ALL
  SELECT key,value
  FROM json_2,json_each(json_2.payload)
), input_json_value AS (
  -- Generate INPUT result - Step 2 - use json_object_agg to create JSON { "id" : "1", "name" : "xyz" }
  SELECT json_object_agg(merged.key,merged.value) AS data 
  FROM merged
), input_json AS (
  -- Generate INPUT JSON as expected { "INPUT" : { "id" : "1", "name" : "xyz" } }
  SELECT json_object_agg('INPUT',input_json_value.data) AS data 
  FROM input_json_value
)
  -- Generate final reult
SELECT json_object_agg('ROOT',input_json.data) 
FROM input_json;