I have XML data to update, and I have only the SQL query to do that. How this can be done ?
I used
UPDATE TABLE
SET ARCH = 'XML DATA'
WHERE name='xxx';
but when pasting the XML code on the shell, it kind of compiles, knowing that the code has some special characters of course like the ' character.
I am on Ubuntu server shell.
update TABLE NAME set COL NAME=xml(REPLACE(OLDXML::text,'</NODE NAME>', NEW VALUE TO UPDATE ||'</NODE NAME>')) where name='xxxx'
Example.
CREATE TABLE notify_test (id integer NOT NULL , notifyxml text)
select * from notify_test
insert into notify_test values(1,'<root><from>Core Integra</from><fromId>[email protected]</fromId><to></to><cc></cc><bcc></bcc><subject>Accoutn created</subject><body><![CDATA[<div class="container" style="width:800px; margin:auto;font-family: Helvetica Neue, Helvetica, Arial, sans-serif; color: #696969;font-size:14px;"><p>Dear {!User.Username}, </p> <p> Your account is created .Your login details: </p> <p> Username : {!User.Email} </p><p> Password: {!password} </p> <p style="line-height:15px;">Best Regards,</p><p>Core Integra Team</p> <img style="width:76px;height:24px;" alt="OrgUnit Small Logo" src=""></div>]]></body><replyTo /></root>')
-------------------------------------------------
CREATE OR REPLACE FUNCTION public.usf_cic_update_templatexml(
templateid integer,
to_mail text,
cc_mail text)
returns integer as
$BODY$
declare xml text;
declare ccxml text;
begin
SELECT notifyxml into xml from notify_test where id =templateid;
raise notice 'Value: %', to_mail;
raise notice 'Value: %', cc_mail;
if to_mail!='' then
update notify_test set notifyxml=xml(REPLACE(xml::text,'</to>', to_mail ||'</to>')) where id =templateid;
if cc_mail!='' then
SELECT notifyxml into ccxml from notify_test where id =templateid;
update notify_test set notifyxml=xml(REPLACE(ccxml::text,'</cc>', cc_mail ||'</cc>')) where id =templateid;
end if;
end if;
return 1;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
