XML : Can I use Oracle 11g's updatexml function to insert a value into an element without also adding the namespace to the tag that I'm inserting?

Let's say that I have many XML documents similar to the following XML document:

  <a xmlns="urn:www.someSite.com/myModel">    <b>      <c>my value</c>    </b>    <d>      <e>my other value</e>      <f>even more other values</f>    </d>  </a>    

And I want the document instead to look like this:

  <a xmlns="urn:www.someSite.com/myModel">    <b>      <e>my other value</e>      <f>even more other values</f>    </b>    <d>      <e>my other value</e>      <f>even more other values</f>    </d>  </a>    

For the sake of argument, let's say these documents are in the table MY_TABLE, in column XML_DOCUMENT_COLUMN. For the sake of simplicity there's also a PRIMARY KEY column on the table called MY_TABLE_KEY.

Using Oracle 11g's UpdateXML function, I can write the following query:

  update MY_TABLE mt  set mt.XML_DOCUMENT_COLUMN = (      select updatexml(          mt.XML_DOCUMENT_COLUMN,           '/a/b',           '<b>' || extract(              mt1.XML_DOCUMENT_COLUMN,               '/a/d/*',               'xmlns="urn:www.someSite.com/myModel"'          ).getStringVal() || '</b>',          'xmlns="urn:www.someSite.com/myModel"')      from MY_TABLE mt1      where mt1.MY_TABLE_KEY = mt.MY_TABLE_KEY  )    

Long story short, what this says is "update the column with this altered version of the column where the key is the same."

The problem is that this always returns a document that looks like this:

  <a xmlns="urn:www.someSite.com/myModel">    <b xmlns="urn:www.someSite.com/myModel">      <e>my other value</e>      <f>even more other values</f>    </b>    <d>      <e>my other value</e>      <f>even more other values</f>    </d>  </a>    

Now, for various reason, I'm not sure that some other code won't break because of a new xmlns declaration in the middle of the document, so I would really like to remove that before running this on my database. I thought the answer would be to just drop the namespace tag in the UpdateXML call, but after running that as a query, I found that no documents in the database changed.

So finally, the question is - how do I update the document to look like the second document, can I do it with one query, and what does that query look like? Bonus round: what am I doing wrong, and what does the xmlns argument to the UpdateXML function do?

Note: The XML documents and XPaths have been changed to protect guilty parties, and to obscure the project that I'm working on. As such, some of this information may not be accurate, as I'm trying to cross-compile XPaths in my head. I'll change the question to provide any clarification that I can.

No comments:

Post a Comment