RE: valid node xmlparser oracle 10g

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Tue, 3 Nov 2009 15:16:00 +0000
Message-ID: <OFB6E3C165.F6ACD828-ON80257663.005389A0-80257663.0053DDDA_at_ons.gsi.gov.uk>



Here is what you need

select extractvalue(x.xml_data,

'/n1:submit/requestHeader/ns5:language',

'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"') omdata from test_xml x;

OMDATA



English

I'm sure any xml experts on the list will correct me but isn't it a little odd to have a separately defined namespace for each node in the path.

Cheers,

ian

|---------+----------------------------->

| | ganstadba_at_hotmail.|
| | com |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 03/11/2009 15:09 |
| | Please respond to |
| | ganstadba |
| | |
|---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: Ian Cary/ONS_at_ONS | | cc: oracle-l_at_freelists.org | | Subject: RE: valid node xmlparser oracle 10g | >--------------------------------------------------------------------------------------------------------------|

Thanks for the response, much appreciated.

I was able to get that out also, but I can't get out the single value 'English' from ns5:language

I'm trying variations of this
select
samplexml.xml.extract('n1:submit/requestHeader/ns5:language','xmlns:n1="ca/j

oe/test/autotype/submitrequest" requestHeader xmlns = "ca/joe/test/autotype/submitrequest" xmlns:ns5 ="ca/joe/test/autotype/testcommonrequest"').getStringVal() omdata from
(select xmltype('<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:customerInteract

ionType>

        <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>') xml from dual) samplexml

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ian Cary
Sent: Tuesday, November 03, 2009 9:51 AM To: ganstadba_at_hotmail.com
Cc: mark-clist_at_npsl.co.uk; oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
Subject: RE: valid node xmlparser oracle 10g

Hi,

I loaded the xml you sent into a 10g database here and ran;

select
extract(x.xml_data,'/n1:submit','xmlns:n1="ca/joe/test/autotype/submitreques

t"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance') omdata from test_xml x

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



This email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) In
case of problems, please call your organisation’s IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.


For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
i0zX+n{+i^
Received on Tue Nov 03 2009 - 09:16:00 CST

Original text of this message