converting from extract to xmltable new syntax

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Tue, 10 Nov 2009 13:56:56 -0500
Message-ID: <SNT130-w21496B3774423F12C3814EA6AB0_at_phx.gbl>

Oracle 10.2.0.4

create table mike_test(filename varchar2(20),xml_data xmltype);

insert into mike_test values ('test.xml', '<n1:submit xmlns:n1 = "ca/joe/test/autotype/submitrequest" xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance">

    <requestHeader xmlns = "ca/joe/test/autotype/submitrequest">

<ns1:customerInteractionType xmlns:ns1 ="ca/joe/test/autotype/testcommonrequest">ContactCentre</ns1:customerInteractionType>
<ns2:serviceRequestUserId xmlns:ns2 ="ca/joe/test/autotype/testcommonrequest">N296922</ns2:serviceRequestUserId>
<ns3:serviceConsumer xmlns:ns3 ="ca/joe/test/autotype/testcommonrequest">Odorrmax</ns3:serviceConsumer>
<ns4:serviceRequestTimestamp xmlns:ns4 ="ca/joe/test/autotype/testcommonrequest">2009-10-29T16:13:26.524-04:00</ns4:serviceRequestTimestamp>
<ns5:language xmlns:ns5 ="ca/joe/test/autotype/testcommonrequest">English</ns5:language>
<ns6:referenceID xmlns:ns6 ="ca/joe/test/autotype/testcommonrequest">A26P83Z3</ns6:referenceID>
    </requestHeader>
</n1:submit>');
commit;

I already know that the use of all the namespaces is a little nuts but I'm forced to work with it. After help from this list I have the following to extract ns5:language

select x.xml_data.extract('/n1:submit/requestHeader/ns5:language/text()', 'xmlns:n1="ca/joe/test/autotype/submitrequest" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="ca/joe/test/autotype/submitrequest" xmlns:ns5="ca/joe/test/autotype/testcommonrequest"').getStringVal() omdata from mike_test x
where filename = 'test.xml'

which gives me "English"

I am now trying to use xmltable to do the same as extract is deprecated in 11g and xmltable uses xquery v2.0. My issue is once again figuring out how the namespaces fit in. I've gone through the docs and found a nice little listing by IBM http://www.ibm.com/developerworks/data/library/techarticle/dm-0611saracco/ but I still don't have it down. I've got the following but it is definitely wrong.

select
xx.language
from
mike_test,xmltable(xmlnamespaces('n1=ca/joe/test/autotype/submitrequest' as "n1"),  '/n1:submit/requestHeader declare
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="ca/joe/test/autotype/submitrequest"' passing xml_data
columns
language varchar2(20) path 'declare ns5="ca/bell/oms/autotype/omscommonrequest"; ns5:language' ) xx
where filename = 'test.xml'

Any help is appreciated

Mike                                                

---

Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you. http://go.microsoft.com/?linkid=9691817

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 10 2009 - 12:56:56 CST