By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tt ( xml_val XMLTYPE );
INSERT INTO tt ( xml_val )
VALUES (
XMLTYPE( '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>XXXXXX</Author>
<LastAuthor>UCB User</LastAuthor>
<Created>2019-10-31T13:04:09Z</Created>
<Version>14.00</Version>
</DocumentProperties>
<a>5</a>
</Workbook>' )
);
1 rows affected
SELECT UPDATEXML(
xml_val,
'/Workbook/o:DocumentProperties/o:Created/text()',
'2020-01-08',
'xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40"'
) AS updated_xml
FROM tt;
| UPDATED_XML |
|---|
| <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"><DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>XXXXXX</Author><LastAuthor>UCB User</LastAuthor><Created>2020-01-08</Created><Version>14.00</Version></DocumentProperties><a>5</a></Workbook> |
SELECT XMLQuery(
'declare default element namespace "urn:schemas-microsoft-com:office:spreadsheet"; (: :)
declare namespace o = "urn:schemas-microsoft-com:office:office"; (: :)
copy $i := $x modify
( for $j in $i/Workbook/o:DocumentProperties/o:Created
return replace value of node $j with $v )
return $i'
PASSING xml_val AS "x",
'2020-01-08' AS "v"
RETURNING CONTENT
) AS updated_xml
FROM tt
| UPDATED_XML |
|---|
| <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"><DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>XXXXXX</Author><LastAuthor>UCB User</LastAuthor><Created>2020-01-08</Created><Version>14.00</Version></DocumentProperties><a>5</a></Workbook> |