Re: Question: SQL, XPath and several namespaces

Uwe M. Kuechler schrieb:
> 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
>

This one works for me:
  SELECT extractvalue(request , '/*[local-name()=''PutOrderPurchaseIn'' and
namespace-uri()=''http://www.mysite.de/schemas/putorderpurchasein/'']/CommonDataIn/endUserId','xmlns="http://www.mysite.de/schemas/commondatain/"') x

   FROM xml_tab
  WHERE trxid = 550000005559889;

BTW, you can get a very comprehensive answers to questions related to XML processing in Oracle in the OTN XML DB forum, the maintainer, Martin Drake is ( IMHO ) an absolute guru and answer literally every question.

Best regards

Maxim Received on Thu Aug 03 2006 - 13:43:27 CDT