UPDATEXML when there is namespaces

Question

I have to update value in 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>2019-10-31T13:04:09Z</Created>
                <Version>14.00</Version>
            </DocumentProperties>
            <a>5</a>
        </Workbook>

In my case this XML is in table tt field xml_val.

Goal XPath is /Workbook/DocumentProperties/Created with value 2019-10-31T13:04:09Z and have to be replaced with 2020-01-08.

I tied this code:

select UPDATEXML(xml_val,
   '/Workbook/DocumentProperties/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"').getClobVal() as last
from tt;

//.getClobVal() in the end is because ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s] (here)

Code above doesn't change anything. I thing this is because there is another namespace declared in DocumentProperties tag. But I don't know how to declare namespaces in UPDATEXML clause.

When I tried to update value in /Workbook/a with this code, it works corect:

select UPDATEXML(xml_val,
   '/Workbook/a/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"').getClobVal() as last
from tt;

Different namespace combination that I tried and not works:

--1

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"

--2

xmlns="urn:schemas-microsoft-com:office:office"
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"

--3

xmlns="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"

--4

xmlns="urn:schemas-microsoft-com:office:office"

NOTE: I can't remove the namespace declaration in DocumentProperties tag because this XML is part from Excel-XML format file

score 1 · Accepted Answer · answered Jan 08 '20 at 13:27

The DocumentProperties element and its children are in the namespace urn:schemas-microsoft-com:office:office which has the shortcut o; you need to prefix those elements with their namespace in the Xpath:

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> |

db<>fiddle here

However, UPDATEXML is deprecated and you should use XMLQUERY:

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

db<>fiddle here

UPDATEXML when there is namespaces

1 Answers1

Linked