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?
Just try the xpath : //Content/DocType/Info/DocumentSource/text()