Oracle XMLQuery is corrupting the namespace

Question

Oracle version 11.2

Below is a cut down version of an XMLQuery i'm running on an XMLType column. When I run the query, which simply parses and recreates the stored XML, the tsxm namespace (that is not equal to the default namespace ) gets changed. This query does nothing and could easily be rewritten, but the real (much bigger) query uses this same methodology so this is why i'm posting the question in this format. If I change the tsxm namespace definition to be the same as the default namespace :

xmlns:tsxm="http://schemas.thomson.com/ts/20041221/tsip"

then the problem goes away, but in the real application this is not possible.

create the table:

CREATE TABLE XML_DOCUMENT_TMP
(
  DOCUMENT_ID   NUMBER(12)                      NOT NULL,
  XML_DATA      SYS.XMLTYPE                     NOT NULL,
  CREATED_DATE  TIMESTAMP(6)                    NOT NULL
);

Insert some data:

insert into XML_DOCUMENT_TMP
(document_id,created_date,xml_data)
values(1,sysdate,'<patent  xmlns="http://schemas.thomson.com/ts/20041221/tsip" 
xmlns:tsip="http://schemas.thomson.com/ts/20041221/tsip" 
xmlns:tsxm="http://schemas.thomson.com/ts/20041221/tsxm"  
tsip:action="replace" tsip:cc="CA" tsip:se="2715340" tsip:ki="C">
<accessions tsip:action="replace">
    <accession tsip:src="wila" tsip:type="key">CA-2715340-C</accession>
    <accession tsip:src="tscm" tsip:type="tscmKey">CA-2715340-C-20150804</accession>
</accessions>
<claimed tsip:action="replace">
    <claimsTsxm tsip:lang="en">
        <tsxm:heading tsxm:align="left">We Claim:</tsxm:heading>
        <claimTsxm tsip:no="1" tsxm:num="1" tsip:type="main">1.  power.</claimTsxm>
    </claimsTsxm>
</claimed>

');

Run the XMLQuery:

WITH tmpTable AS (
SELECT * FROM XML_DOCUMENT_TMP cm )
SELECT tt.xml_data ,
XMLQuery('declare default element namespace  "http://schemas.thomson.com/ts/20041221/tsip";
      declare namespace  tsip="http://schemas.thomson.com/ts/20041221/tsip";
      declare namespace  tsxm="http://schemas.thomson.com/ts/20041221/tsxm"; 

      let $patsLus := $m/patent/*

      return          
      <patent>{$m/patent/@*}
      {
        for $i in $m/patent/*
            return    $i
      }
      </patent>' 
            PASSING tt.xml_data as "m"   RETURNING CONTENT) newXml 
 FROM tmpTable tt
 WHERE tt.document_id in (1);

Returns:

<patent xmlns="http://schemas.thomson.com/ts/20041221/tsip"    xmlns:syspfx_AT="http://schemas.thomson.com/ts/20041221/tsip" syspfx_AT:action="replace" syspfx_AT:cc="CA" syspfx_AT:se="2715340" syspfx_AT:ki="C"><accessions xmlns="http://schemas.thomson.com/ts/20041221/tsip" action="replace">
<accession src="wila" type="key">CA-2715340-C</accession>
<accession src="tscm" type="tscmKey">CA-2715340-C-20150804</accession>
</accessions>
<claimed xmlns="http://schemas.thomson.com/ts/20041221/tsip" action="replace">
<claimsTsxm lang="en">
<syspfx_1:heading xmlns:syspfx_1="http://schemas.thomson.com/ts/20041221/tsxm" syspfx_1:align="left">We Claim:</syspfx_1:heading>
<claimTsxm no="1" xmlns:syspfx_1="http://schemas.thomson.com/ts/20041221/tsxm" syspfx_1:num="1" type="main">1.  power.</claimTsxm>
</claimsTsxm>
</claimed>
</patent>

So , the question is, what is causing the tsxm namespace declaration to be changed to syspfx_AT and the tsxm namespace prefix to xmlns:syspfx_1?

Any ideas much appreciated.

Faunia

Answer 1

This appears to be expected behaviour, according to My Oracle Support doc ID 2060374.1. But from a bit of experimentation it seems that wildcarding the namespace in your XPath stops it happening; so:

  <patent>{$m/*:patent/@*}
  {
    for $i in $m/*:patent/*
        return    $i
  }
  </patent>' 

With your original data (in 11.2.0.4), and serialized to format it more readably:

 WITH tmpTable AS (
SELECT * FROM XML_DOCUMENT_TMP cm )
SELECT tt.xml_data ,
XMLSerialize(DOCUMENT
XMLQuery('declare default element namespace  "http://schemas.thomson.com/ts/20041221/tsip";
      declare namespace  tsip="http://schemas.thomson.com/ts/20041221/tsip";
      declare namespace  tsxm="http://schemas.thomson.com/ts/20041221/tsxm";

      let $patsLus := $m/patent/*

      return          
      <patent>{$m/*:patent/@*}
      {
        for $i in $m/*:patent/*
            return    $i
      }
      </patent>' 
            PASSING tt.xml_data as "m"   RETURNING CONTENT)
 AS VARCHAR2(4000) INDENT SIZE = 2) ewXml 
 FROM tmpTable tt
 WHERE tt.document_id in (1);

XML_DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NEWXML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<patent xmlns="http://schemas.thomson.com/ts/20041221/tsip" xmlns:tsip="http://schemas.thomson.com/ts/20041221/tsip" xmlns:tsxm="http://schemas.thomson.com/ts/20041221/tsxm" tsip:action="replace" tsip:cc="CA" tsip:se="2715340" tsi
p:ki="C">
  <accessions tsip:action="replace">
    <accession tsip:src="wila" tsip:type="key">CA-2715340-C</accession>
    <accession tsip:src="tscm" tsip:type="tscmKey">CA-2715340-C-20150804</accession>
  </accessions>
  <claimed tsip:action="replace">
    <claimsTsxm tsip:lang="en">
      <tsxm:heading tsxm:align="left">We Claim:</tsxm:heading>
      <claimTsxm tsip:no="1" tsxm:num="1" tsip:type="main">1.  power.</claimTsxm>
    </claimsTsxm>
  </claimed>
</patent>
<patent xmlns="http://schemas.thomson.com/ts/20041221/tsip" xmlns:tsip="http://schemas.thomson.com/ts/20041221/tsip" tsip:action="replace" tsip:cc="CA" tsip:se="2715340" tsip:ki="C">                                                
  <accessions xmlns="http://schemas.thomson.com/ts/20041221/tsip" xmlns:tsip="http://schemas.thomson.com/ts/20041221/tsip" tsip:action="replace">                                                                                     
    <accession tsip:src="wila" tsip:type="key">CA-2715340-C</accession>                                                                                                                                                               
    <accession tsip:src="tscm" tsip:type="tscmKey">CA-2715340-C-20150804</accession>                                                                                                                                                  
  </accessions>                                                                                                                                                                                                                       
  <claimed xmlns="http://schemas.thomson.com/ts/20041221/tsip" xmlns:tsip="http://schemas.thomson.com/ts/20041221/tsip" tsip:action="replace">                                                                                        
    <claimsTsxm tsip:lang="en">                                                                                                                                                                                                       
      <tsxm:heading xmlns:tsxm="http://schemas.thomson.com/ts/20041221/tsxm" tsxm:align="left">We Claim:</tsxm:heading>                                                                                                               
      <claimTsxm tsip:no="1" xmlns:tsxm="http://schemas.thomson.com/ts/20041221/tsxm" tsxm:num="1" tsip:type="main">1.  power.</claimTsxm>                                                                                            
    </claimsTsxm>                                                                                                                                                                                                                     
  </claimed>                                                                                                                                                                                                                          
</patent>                                                                                                                                                                                                                             

That isn't identical to your original but doesn't have the namespace corruption any more. Whether you can do that in your real query, and whether the wildcarding causes any issues for that, are another matter...

Endorsed

Alex - Googling "MoS doc ID 2060374.1" returns nothing, but I don't really care. Your namespace wildcarding worked a treat in the real query. Thanks a lot for your help. – Faunia

MoS is My Oracle Support; you need an Oracle account to log in and probably a support identifier to see that document. But it doesn't tell you much except someone else had this problem before, and Oracle said it wasn't a problem... – Endorsed

Alex - Im assuming that the varchar2(4000) conversion that you have used is to get rid of the embedded namespace declarations in <patent> children. In the real query this won't work because the 4000 chars is too small. Is there another way round this problem? – Faunia

@Faunia - no, that's part of the XMLSerialize call, which is just to format it more neatly for display. The XMLQuery with the wildcards works on its own. – Endorsed

Sorry your correct. Sticking with my previous question, Is there a way to remove the namespace declarations in the accessions and claimed elements? – Faunia

Not that I've been able to find. no. If I get time I'll come back to this later, but I don't currently see a way of controlling it. It might be worth asking a new question just about that. – Endorsed