Creating XML out of JSON array in PostgreSQL

Question

I have a table with a column containing data like this: array of n json elements

[
  {"ref":"3455","desc":"My Product 1","price":"4","quant":"90"},
  {"ref":"3455","desc":"My Product 2","price":"5","quant":"80"}
]

I need to parse/iterate each of JSON element. In the example I have 2. The result will be a custom string (actually XML string).

XML result expected:

<items>
  <item>
    <ref>3455</ref>
    <desc>My Product 1</desc>
    <price>4</price>
    <quant>90</quant>
  </item>
  <item>
    <ref>3455</ref>
    <desc>My Product 2</desc>
    <price>5</price>
    <quant>80</quant>
  </item>
</items>

What is the best way to achieve this?

thank you!

Please Edit your question and add the expected output based on your sample data. Formatted text please, no screen shots. — user330315
– user330315, Commented Nov 8, 2017 at 12:31
There is no dedicated tools in Postgres to convert json to xml. I think such a conversion may be really easy in a client app. Search for a solution in your favorite language, e.g. Convert JSON to XML in Python — klin
– klin, Commented Nov 8, 2017 at 14:15

Dmitry · Accepted Answer · 2017-11-08 18:29:27Z

1

In my solution I presume that objects in your JSON array have the same structure. So we create an SQL type for this objects:

DROP TYPE IF EXISTS item_type;
CREATE TYPE item_type 
  AS ("ref" int, "desc" VARCHAR(500), price FLOAT, quant INTEGER);
--ref and desc are SQL key words 
--so you need to specify that they actually are column names

The demo table looks like this:

CREATE TABLE items
(
    id SERIAL NOT NULL
        CONSTRAINT items_pkey
            PRIMARY KEY,
    content jsonb DEFAULT '[]'::jsonb NOT NULL
);

The query:

SELECT xmlelement(name items, (--create the root element "items"
  SELECT xmlagg(--aggregate "item" elements
      xmlelement(NAME item, --create "item" elements
                 xmlforest(t."ref", t."desc", t.price, t.quant)--create "item" element content
      )
  )
  FROM
    (
      SELECT (jsonb_populate_recordset(NULL :: item_type, items.content)).*--create records ot of JSON array
      FROM items
      WHERE items.id = 1 --you can remove where if you want all rows
    ) AS t
))

Explanation:
From inside out; (1) create SQL records of the type we created earlier (the item_type) out of the JSON array using jsonb_populate_recordset, (2) create the content of the <item> nodes using xmlforest, (3) create the <item> element itself using xmlelement, (4) aggregate <item> elements using xmlagg, create the root element <items> using xmlelement.

SQLFiddle - it runs but it looks like SQLFiddle have problems outputting XML results so I cast the result to TEXT.

edited Nov 8, 2017 at 18:29

answered Nov 8, 2017 at 18:18

Dmitry

7,29614 gold badges42 silver badges42 bronze badges

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

1 Comment

Dmitry Over a year ago

@user3643038 Please accept and up vote the answer if it helped you.

Collectives™ on Stack Overflow

Creating XML out of JSON array in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related