Question: SQL, XPath and several namespaces

From: Uwe M. Kuechler <gg.3.ukuechle_at_spamgourmet.com>
Date: 3 Aug 2006 10:08:22 -0700
Message-ID: <1154624902.146415.188170@b28g2000cwb.googlegroups.com>

Hi all,
my customer needs to update attributes within an xmltype column. So far I haven't figured out how to deal with nested elements containing another namespace.

This is an example table:
CREATE TABLE XML_TAB
(

  TRXID    NUMBER(15)                           NOT NULL,

  REQUEST SYS.XMLTYPE
);

Here's an example of what the XML looks like:

<?xml version="1.0" encoding="ISO-8859-1"?> <PutOrderPurchaseIn
xmlns="http://www.mysite.de/schemas/putorderpurchasein/">
<trxId>550000005559889</trxId>
<portfNo>0176744001</portfNo>
<isin>LU0815900775</isin>
<trxAmtTrxCcy>4000.00</trxAmtTrxCcy>
<trxCcy>EUR</trxCcy>
<acctUseMainAcctFlag>true</acctUseMainAcctFlag>
<orderer1Name>Doe</orderer1Name>
<orderer1FirstName>Jane</orderer1FirstName>
<ns1:CommonDataIn

xmlns:ns1="http://www.mysite.de/schemas/commondatain/">

  <ns1:endUserId>53111861</ns1:endUserId>
  <ns1:endUserName>Doe</ns1:endUserName>
  <ns1:endUserFirstName>John</ns1:endUserFirstName>
  <ns1:procurerId>81555000</ns1:procurerId>

</ns1:CommonDataIn>

</PutOrderPurchaseIn>

This is what I tried to do (among many others) to select attributes in the namespace ns1:

SELECT extractvalue(request

           , '/PutOrderPurchaseIn/CommonDataIn/endUserId', 'xmlns="http://www.mysite.de/schemas/commondatain/"'

           ) x
  FROM xml_tab
 WHERE trxid = 550000005559889;

X

---

As you can see, a null value is returned. Now, it's no problem to select or update the attribute "trxId" in namespace "...putorderpurchasein" with the very same method, but how would I do this for any attribute in Namespace ns1, like "endUserId"? It looked so trivial, yet it isn't.

Regards,
Uwe Received on Thu Aug 03 2006 - 12:08:22 CDT