CODEWITHSUNDEEP
xmlpostgresql
kishore
kishore

Reputation: 672

Append value to an XML SELECT result

I am trying to fetch an XML from a table and append an XML element to it. I don't want to physically update the XML in DB. Only the SELECT result has to be modified.

Suppose we have an XML field with this blob,

<Test>
   <ID>1</ID>
   <Name>test123</Name>
</Test>

and we want to append a Type element to this 

<Test>
   <ID>1</ID>
   <Name>test123</Name>
   <Type>Employee</Type>
</Test>

how would we achieve this using postgres XML functions? The postgres documentation for XML functions here does not seem to have an add_child method. In fact, postgres has offered very few methods for XML, unlike Strings. I did not find any method to modify an XML data using SQL queries without having to fetch them from DB, modify them in the application and update the table with the modified entry. Although my use-case does not require me to modify the data in DB, I would like to make use of any such function along with a SELECT query to achieve my use-case. Is there a way I can achieve this with a single query?

Upvotes: 0

Views: 730

Answers (1)

Adam
Adam

Reputation: 5599

Maybe this is not the nicest way, but it will do what you want:

SELECT 
    '<Test>' ||
    xmlconcat(
        xmlagg(parts),
        xmlelement(name "Type", null, 'Employee')
    ) ||
    '</Test>'
FROM
    unnest(
        xpath('/Test/*', '<Test><ID>1</ID><Name>test123</Name></Test>')
    ) AS parts;

First, it takes all children of Test element with xpath() function. Then those parts are aggregated and concatenated with new Type element. At the end the root element Test is added again.

Upvotes: 1

Related Questions