How to insert separate Node into xml field in Postgresql?

Question

PostgreSQL

I have the following table

create table xml_test(
id serial primary key,
user_id integer,
xml_test_f xml
)

let's say I ran the command

insert into xml_test(user_id, xml_test_f) values (1, xml('<Audit>
                       		<User userId = "1" login = "user">
                       			<Action name = "REPLACEMENT" billId = "22">
                       				<Status>OPEN</Status>
                          </Action>
                       		</User>
                         </Audit>'));

and inserted into it resp. xml

<Audit>
  <User userId = "1" login = "user">
    <Action name = "REPLACEMENT" billId = "22">
      <Status>OPEN</Status>
    </Action>
  </User>
</Audit>

now I want to insert a new Action node

update xml_test 
  set xmlelement(name 'Action', xmlattributes(23 as billId, 'REPLACEMENT' as 'name'), '<Status>CLOSED</STATUS>') 
  where user_id = 1;

well, this is so offhand, because ideally, instead of 'Status', there should be another node
, that is, I want to insert a similar xml node inside the User node with userId = 1

<Action name = 'REPLACEMENT' billId = 23>
  <Status>
    CLOSED
  <Status>
</Action>

but I can't figure out how to describe the sql query for this

Answer 1

After a week and a half of studying documentation, articles and video tutorials, I found the following solution, which boils down to bringing xml to text
Let's say I have a User root and its Action node descendants

select unnest(xpath('//user', xml_test_f))::text from xml_test where id = 3;

<user userid="1" login="user">
  <action billid="22" name="REPLACEMENT">
    <status>CLOSED</status>
  </action>
  <action billid="11" name="REPLACEMENT"><status>OPEN</status></action><action/>
</user>

A closed empty node is inserted at the end of the action list when the document is initially created.
The following function is created

create or replace function addNode(xmlId integer, newNode xml) 
returns void as 
$BODY$
declare oldXml text;

begin

select unnest(xpath('//user', xml_test_f))::text into oldXml from xml_test where id = 3;
update xml_test set  xml_test_f=xml(REPLACE(oldXml::text,'<action/>', newNode::text ||'<action/>')) where id = xmlId;

end
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;

In which we select a tree from the user and edit it. When editing, the closed node is replaced by the input node + the closed node newNode::text ||''.
Now this function can be used

Select addNode(3, xmlelement(name action, xmlattributes('11' as billId, 'REPLACEMENT' as name),
              xmlelement(name status, 'OPEN')));