Oracle XMLQuery inserting unwanted namespace

Question

Oracle 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 unwanted default and tsip namespaces get inserted into the child elements of the parent. Note that the tsxm namespace does not get inserted, this is because it is not equal to the default namespace 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.

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 (which must have the namespaces as is):

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>
</patent>
');

Run the XMLQuery:

Note the need for namespace wildcarding is explained here

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


  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: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/tsip" tsxm:align="left">We Claim:</tsxm:heading>
            <claimTsxm tsip:no="1" xmlns:tsxm="http://schemas.thomson.com/ts/20041221/tsip" tsxm:num="1" tsip:type="main">1.  power.</claimTsxm>
        </claimsTsxm>
</claimed>

How do I get rid of the unwanted namespaces created in the accessions and claimed elements. Any suggestions appreciated.

One quick & dirty way would be to process your output with XSLT using the identity transform. Seems overkill though, I'd be reluctant to call that your best solution unless it turns out to be the only one, but I doubt that. — Flynn1179, Aug 03 '16 at 07:43
@Flynn1179 This solution (doing the xml processing in XMLQuery) is precisely to avoid using xslt, which it too slow (for large XMLTypes). — DS., Aug 03 '16 at 08:06
Could you try 'DBMS_XMLDOM.REMOVEATTRIBUTE'? I am not sure if it is a working solution. — Hash, Aug 09 '16 at 14:00

Graham Nicol · Accepted Answer · 2016-08-11T10:18:40.663

If you play around with the various values of the namespaces you can see that while the top <patent> level the namespaces are declared and included because of the declarations you make, at the child element level this information isn't used in the way that you are expecting.

The XQuery is extracting the namespaces based on those in use in the nodes being considered in that execution loop, independent of the documents as a whole. This is why they get "re-declared" each time the XQuery goes round the loop.

Other articles explain that what you're trying to do is to "Parse" the data as well as "Extract" it, which is true to an extent, and so XSLT is the right tool rather than XQuery.

One external link I found which has an XQuery way of stripping the namespaces and so returning you the "raw" XML is here.

Applying that code to your XQuery has got me to:

SELECT xmlquery('xquery version "1.0"; (: :)
             declare default element namespace 
                        "http://www.somewherein.uk/ns/1.0"; (: :)

             declare function local:strip-namespace($inputRequest  as element()) as element()
             {
                element {xs:QName(local-name($inputRequest ))}
                {
                  for $child in $inputRequest /(@*,node())
                    return
                      if ($child instance of element())
                      then local:strip-namespace($child)
                      else $child
                }
             }; (: :)

             <patent>
             {
             for $s in /*:patent/*
              return local:strip-namespace($s)
             }
             </patent>' 
             PASSING cmf.XML_DATA 
             RETURNING content)
FROM XML_DOCUMENT_TMP cmf WHERE cmf.DOCUMENT_ID=1

Some further editing got me to the below, which I think is what you were after (namespaces defined at the patent level)

SELECT xmlquery('xquery version "1.0"; (: :)
             declare default element namespace 
                        "http://www.somewherein.uk/ns/1.0"; (: :)

             declare function local:strip-namespace($inputRequest as element()) as element()
             {
                element {fn:name($inputRequest)}
                {
                  for $child in $inputRequest /(@*,node())
                    return
                      if ($child instance of element())
                      then local:strip-namespace($child)
                      else $child
                }
             }; (: :)

             <patent>
             {
             for $s in /(*:patent, node())
              return local:strip-namespace($s)
             }
             </patent>' 
             PASSING cmf.XML_DATA 
             RETURNING content)
FROM XML_DOCUMENT_TMP cmf WHERE cmf.DOCUMENT_ID=1;

As commented below that led to some duplication in the loop code, due to some issues in the XPath. It also meant that the txsm namespace was declared a couple of times; the XQuery declares it "the first time" it encounters the namespace being used it as it walks that tree branch meaning that if there are siblings that use the ns then it will be declared multiple times. By moving the explicit placement of the declaration back to the parent node we can eliminate that.

SELECT xmlquery('xquery version "1.0"; (: :)
             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"; (: :)

             declare function local:strip-namespace($inputRequest as element()) as element()
             {
                element {fn:name($inputRequest)}
                {
                  for $child in $inputRequest /(@*,node())
                    return
                      if ($child instance of element())
                      then local:strip-namespace($child)
                      else $child
                }
             }; (: :)

             <patent xmlns:tsxm="http://schemas.thomson.com/ts/20041221/tsxm" xmlns:tsip="http://schemas.thomson.com/ts/20041221/tsip">
             {
             for $s in /*:patent/*
              return local:strip-namespace($s)
             }
             </patent>' 
             PASSING cmf.XML_DATA
             RETURNING content)
FROM XML_DOCUMENT_TMP cmf WHERE cmf.DOCUMENT_ID=1;

I've seen that link but I couldn't get the "declare function" to work. I get: ORA-19193: XQST0045: It is a static error if the function name in a function declaration is in one of the following namespaces: http://www.w3.org/XML/1998/namespace ... If you can post a working amended version of my xQuery then you can happily have the points. — DS., Aug 09 '16 at 15:13
Not perfect yet, but by adding the `local` namespace to the function delcaration I've got it as far as calling the function. It now has a type mismatch (of `%s` against `%s` bizarrely) — Graham Nicol, Aug 09 '16 at 16:38
That's a decent effort. I notice that you haven't declared $inputRequest , as suggested in your link (since it produces ORA-19112 error) I wonder if this is the problem? — DS., Aug 10 '16 at 08:23
It could be, I think the original poster put that in to resolve this type of problem as it seems to be getting hung up in the `{xs:QName(local-name($inputRequest ))}` block. — Graham Nicol, Aug 10 '16 at 10:26
You have removed the namespace's but you now have 2 patent elements, wrapped in a patent element! — DS., Aug 11 '16 at 09:10
the points bonus seems to have disappeared, so all I am able to do is up vote your answer. — DS., Aug 11 '16 at 10:05
I think it expired at the end of yesterday. No matter, it's been a fun problem to work on. — Graham Nicol, Aug 11 '16 at 10:22

score 1 · Answer 2 · answered Aug 11 '16 at 10:02

Amending @Graham Nicol's 2nd solution slightly, this seems to give the correct answer:

SELECT xmlquery('xquery version "1.0";
         declare default element namespace 
                    "http://schemas.thomson.com/ts/20041221/tsip";

         declare function local:strip-namespace($inputRequest as element()) as element()
         {
            element {fn:name($inputRequest)}
            {
              for $child in $inputRequest /(@*,node())
                return
                  if ($child instance of element())
                  then local:strip-namespace($child)
                  else $child
            }
         }; 


         <patent>{/*:patent/@*}
         {
         for $s in /*:patent/*
          return local:strip-namespace($s)
        }
         </patent>
         ' 
         PASSING cmf.XML_DATA 
         RETURNING content)
FROM XML_DOCUMENT_TMP cmf WHERE cmf.DOCUMENT_ID=1;

Yes, that seems to get to the same point. The one difference we still have is the "automatic" versus "explicit" listing of the namespaces in the top `` element, which causes a change in how the namespaces "distribute" themselves. — Graham Nicol, Aug 11 '16 at 10:21
@graham we can't hard code the ns declarations in because they vary, they've got to come from the xml. We only have ns declarations in the root element so {/*:patent/@*} should be ok. — DS., Aug 11 '16 at 12:35

Oracle XMLQuery inserting unwanted namespace

2 Answers2