How to add namespace information when using the XMLtype constructor and passing over XML string data?

Please let me add the following question...

I tried the solution for and it's looking good, but as I try to insert the contents of the nodeBuffer into an XMLtype table, oracle db complains about the following "ORA-30937" and "ORA-06512".
The reason seem to be the missing namespace information.

  xmlvar  XMLtype;
  nodebuffer clob;
...
  xmlvar := XMLType(nodebuffer);
   insert into C_AL2 values (xmlvar);

When I artificially edit my code and insert this information by hand, the code works.
nodebuffer := '<C  xmlns="http://www.xxx.com/ABC" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">...</C>

Has anybody got an idea?
   
Johann1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
For you to be able to cast a CLOB as an XMLTYPE, it needs to be valid XML.

If you have something like <a><b:c>Hello</b:c></a> and don't have the namespace declared, it's not valid XML.

If you can't control how you build the CLOB then I think you'll need to do a REPLACE to add the namespace.

If you can provide a little more detail about the larger picture, I might be able to suggest alternatives.
Johann1Author Commented:
Thank you for your comments. I will give you some more details. So, this is my context:

The big task is to get XML data of a give schema into XMLtype tables inside the oracle database.
In order to achieve this, I created an auxiliary XMLtype table (as I just own the 10g database, I cannot use the binary XML type). Now, I insert one full xml file content into the auxiliary table.
Next step is to process the content of this single record inside the aux table in order to distribute it into the 'productive' xmltype tables. All xmltype tables, mentioned so far, are using the same xml schema S1. The source XML file (inserted into the aux table) uses an aggregating XML type from S1 containing sub elements that use other XML types from S1. For each of these sub elements there is a 'productive' XMLtype table inside the database, So, I read  one record from the aux table (one xml element) and want to distribute its subelements into the XMLtables of the respective XML types.
Here is the sequence...

1. create a DBMS_XMLparser and set a basedirectory in which the source xml file is copied.
2. parse the xml file.
3. get the domdocument form the parser.
4. get the uppermost (and also single) element (root node) using getElementsbytagname.
5. get the childnodes and store them in a domnodelist.
6. loop the domnodelist.
6a. check the nodename and compare it to one of the expected tagnames.
6b. createclob and write the node-xml into the buffer
     DBMS_LOB.CREATETEMPORARY(nodebuffer, TRUE);
     DBMS_XMLDOM.Writetoclob(node, nodebuffer);
6c. call a XMLtype constructor
      xmlvar  XMLtype;
       ...          
      xmlvar := XMLType(nodebuffer);
6d insert the XMLtype into the appropriate productive XMLtype table
      insert into p1table values (xmlvar);

During the last step, oracle tells me that about ORA-30937 and ORA-06512
If I add the namespace artificially in my code by hardcode-overwriting the buffer contents using an example string the insert works and no error message will occur.

So, that's why I ask about a solution to tell the xmltype constructor to insert the namespace information into the xml string I pass to it.

Are these descriptions clear enough? If not, please let me know. Thank you.

slightwv (䄆 Netminder) Commented:
That sounds like a lot of work to loop through nodes of an XML file but anyway, you still have the issue of:  If the fragment (node) you extract has a namespace reference you must address that before you cast nodebuffer as an xmltype.

A lot of the DBMS_XMLDOM functions and procedures have a namespace parameter.  If you want to grab the root node namespace check out DBMS_XMLDOM.GETNAMESPACE.

then you should be able to use one of the node functions to add it to the fragment.

I've played around a little with DBMS_XMLDOM but don't consider myself a real expert with it.

If you can create a small test case and expected results I can see if I can come up with some working code.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Johann1Author Commented:
Thank you for that. Based on your previous comment I played around using DBMS_LOB functionality and managed to disassemble and reassemble the xml string theregy adding a namespace-substring. As I always have a constant namespace this solution will work, although I hoped there would be a more elegant solution ;-) . But I think that's fine for the moment. ...as the next problem has already shown up, as usual ;-)

Thank you very much, slightwv!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.