3 Replies Latest reply: Dec 13, 2013 1:46 AM by odie_63 RSS

    How to add an element with a namespace prefix (Part 2)

    Geraud

      Hi all,

      I previously asked a question about adding an attribute with a namespace prefix to an element that already exists and that declares the namespace prefix here:

      https://forums.oracle.com/thread/2610142

      I received an answer that works, but now I am stumped again when I have to add an element where the element name has the namespace prefix.

      For example, let's say I already have this element:

       

      <A xmlns="namespace" xmlns:def="myns_namespace"/>
      

       

      And I want to add this element:

       

      <def:B/>
      

       

      To produce this:

       

      <A xmlns="namespace" xmlns:def="myns_namespace">
           <def:B/>
      </A>
      

       

      and NOT this:

       

      <A xmlns="namespace" xmlns:def="myns_namespace">
           <def:B  xmlns:def="myns_namespace"/>
      </A>
      

       

      This does not work:

       

      SELECT 
      xmlserialize(document
          appendChildXML(
           xmltype('<A xmlns="namespace" xmlns:def="myns_namespace"/>')
          , '/A'
          , xmlelement("def:D")
          , 'xmlns="namespace" xmlns:def="myns_namespace"'
          )
        indent)
      FROM dual;
      

       

      Because of this error:

       

      ORA-31011: XML parsing failed

      ORA-19202: Error occurred in XML processing

      LPX-00234: namespace prefix "def" is not declared

      Error at line 1

      31011. 00000 -  "XML parsing failed"

      *Cause:    XML parser returned an error while trying to parse the document.

      *Action:   Check if the document to be parsed is valid.

       

      Is there any way to do this without the child element having the duplicate namespace declaration?

      My oracle version is:

      Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

        • 1. Re: How to add an element with a namespace prefix (Part 2)
          odie_63

          Hi,

           

          This one's tricky, so tricky that I think it's not possible using Oracle built-in XML DML functions.

          Even XQuery Update cannot do it (for now) because, likewise, the prefix is always redeclared at child level.

           

          The only thing I can think of is XSLT (or maybe DOM manipulation) :

          SQL> select xmlserialize(document

            2           xmltransform(

            3             xmltype('<A xmlns="namespace" xmlns:def="myns_namespace"/>')

            4           , xmltype(

            5  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

            6     xmlns:def="myns_namespace"

            7     xmlns:ns0="namespace">

            8    <xsl:template match="ns0:A">

            9      <xsl:copy>

          10        <xsl:element name="def:B"/>

          11      </xsl:copy>

          12    </xsl:template>

          13  </xsl:stylesheet>')

          14         )

          15        indent

          16      )

          17  from dual;

           

          XMLSERIALIZE(DOCUMENTXMLTRANSF

          --------------------------------------------------------------------------------

          <A xmlns="namespace" xmlns:def="myns_namespace">

            <def:B/>

          </A>

           

          • 2. Re: How to add an element with a namespace prefix (Part 2)
            Geraud

            That is interesting. I guess I could go the XSLT route, but it seems like overkill.  The fact that this cant be done leads me to believe that I might be taking the wrong approach when building my doc. I guess here is a simplification of the issue that might cover my needs:

             

             

            I have a table of parent ids and names and a table that contains parent id's and child names.

             

            In pl/sql:

            --First I loop through the parent table. For each parent

            ----Create the parent elements and attach them to the doc.

            ----Loop through the child table. For each child

            ------Create the child element and attach to the parent.


             

            Maybe I can solve my problem if there is a way to do these steps in a single query?

            • 3. Re: How to add an element with a namespace prefix (Part 2)
              odie_63

              That is interesting. I guess I could go the XSLT route, but it seems like overkill.

              I agree.

               

              I have a table of parent ids and names and a table that contains parent id's and child names.

              Do the children have children too? How many nested levels are there?

              In short, do you want to generate a recursive structure or just a master-detail structure?

              If the former see : How To : Generate a recursive XML structure | Odie's Oracle Blog

              If the latter, then I guess it could be solved in one go using SQL/XML functions.

               

              If you can, please post some sample data and expected output, that should help clarifying.