Now I transfer the system from oracle to postgresql. There is a task: the xml procedure comes in; you need to change it and give it back. In oracle, I made a collection from xml using XMLDOM, changed the collection and then converted it back to xml. The question is how in postgresql to convert xml into a collection of a certain type or can someone tell me another way how to change xml on the go.

  • one
    Can you add an example to play around? An example input and the result expected for this parameter. The pg function has a bunch of functions: postgresql.org/docs/current/static/functions-xml.html But it was not possible to process XML in the database, unlike json. - Shallow
  • I suspect that it would be more convenient to work with json. - ilyaplot
  • unfortunately only xml is transferred to me, it is impossible to change it - heff
  • So what about sample data and the desired result? I would play with the task, but it is not clear what exactly should be obtained. - Fine

1 answer 1

Here is the answer to the question "how to convert xml to a collection of a certain type":

SELECT unnest(xpath('text()', node))::text as val, unnest(xpath('@id', node))::text::int as id FROM unnest(xpath('l', '<root> <l id="1">value 1</l> <l id="2">value 2</l> <l id="3">value 3</l> <l id="4">value 4</l> </root>'::xml )) as node

Change the data, then back to xml. Perhaps it can be called "on the go."