Oracle UpdateXML not accepting a valid xPath

Question

I have an XML in XMLTYPE column, that looks like the following:

<n:ROOT xmlns:app="http://company/acms/content/DocType" xmlns:n="http://company/acms/content/DocType">
   <Content>
      <Definition>
         <Code>DocType</Code>
         <Version>1</Version>
      </Definition>
      <DocType>
         <Info>
            <DocumentType>DocType</DocumentType>
            <DocumentClass>Other</DocumentClass>
            <CustomerID>12323423</CustomerID>
            <FinancialAccountID>12312312</FinancialAccountID>
            <MSISDN>34534534</MSISDN>
            <CustomerType>Consumer</CustomerType>
            <CustomerSubType>Consumer-Platinum</CustomerSubType>
            <FirstName>Name</FirstName>
            <ServiceType>ServiceType</ServiceType>
            <DocumentSource>BadValueHere</DocumentSource>
            <BoxReferenceNumber>BoxXXX</BoxReferenceNumber>
            <Industry>OTHERS</Industry>
            <CreationDate>2015-01-01</CreationDate>
            <DocumentID>6666</DocumentID>
            <CreatedBy>UBIX</CreatedBy>
            <DocumentOrigin>HD</DocumentOrigin>
            <Notes>Document location on File System: /path/to/doc.TIF</Notes>
            <Resource parseType="none">
               <URL>/path/filename.pdf</URL>
               <FileName>filename.pdf</FileName>
               <MimeType>image/tiff</MimeType>
            </Resource>
            <FileType>tiff</FileType>
         </Info>
         <Permissions>
            <GroupPermissions>
               <GroupName>99</GroupName>
               <Permissions>C</Permissions>
            </GroupPermissions>
            <GroupPermissions>
               <GroupName>30</GroupName>
               <Permissions>W</Permissions>
            </GroupPermissions>
            <GroupPermissions>
               <GroupName>66</GroupName>
               <Permissions>R</Permissions>
            </GroupPermissions>
         </Permissions>
      </DocType>
   </Content>
</n:ROOT>

Now I need to update the DocumentSource element, to a new value.

I tried the following query:

SET CONTENT_DATA =  UPDATEXML(CONTENT_DATA,
   '/n:ROOT/Content/DocType/Info/DocumentSource/text()', 'Test')
where CONTENT_ID=6666;

However, when I try to run it, I get Oracle error: SQL Error: ORA-31013: Invalid XPATH expression 31013. 00000 - "Invalid XPATH expression" *Cause: XPATH expression passed to the function is invalid. *Action: Check the xpath expression for possible syntax errors.

Documentation for UpdateXML is on: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions205.htm it talks about 5th element to specify the namespace, but I have had no luck getting it to work :( There are absolutely zero examples on how to use it in my scenario on Google...

Can any expert please help?

score 0 · Accepted Answer · answered Nov 07 '16 at 20:52

0

Just try the xpath : //Content/DocType/Info/DocumentSource/text()

answered Nov 07 '16 at 20:52

SomeDude

13,876
5
21
44

Sorry, deleted old comment - it didnt work on select extract, BUT it works on UPDATEXML! :) Why? I want to understand it better... – Carmageddon Nov 08 '16 at 07:58
I don't understand the Oracle environment in any detail (though I do know how difficult it is to produce good error messages when the development team gets too big...). Basically the syntax of the XPath expression is OK, so the thing that's most likely to be wrong is that the namespace prefix "n" hasn't been bound to any URI. So you either need to find out how this particular XPath API lets you bind namespace prefixes, or you need to rewrite the expression in a way that doesn't need any namespace bindings. @svasa took the latter approach, which is lazy but works fine in this case. – Michael Kay Nov 08 '16 at 10:00
Thanks Michael. I know the error is related to namespace from all my google searches yesterday, but not a single example of how to use the function's 5th parameter for namespace. The update query runs slow.. 2 hours running now, over 10mil records to update. Could it be because of the easy workaround without namespace spcifying is causing it to be slower? – Carmageddon Nov 08 '16 at 10:10
The update operation has failed: SQL Error: ORA-04030: out of process memory when trying to allocate 55600 bytes (qmcxdDecodeIni,qmemNextBuf:Large Alloc) 04030. 00000 - "out of process memory when trying to allocate %s bytes (%s,%s)" *Cause: Operating system process private memory has been exhausted *Action: Any ideas? – Carmageddon Nov 08 '16 at 12:43

Oracle UpdateXML not accepting a valid xPath

1 Answers1