RE: valid node xmlparser oracle 10g
Date: Tue, 3 Nov 2009 14:51:19 +0000
Message-ID: <OF13EB50F6.E79237A2-ON80257663.00514869-80257663.00519B64_at_ons.gsi.gov.uk>
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/submitrequest"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance') omdata
from test_xml x
OMDATA
<n1:submit xmlns:n1="ca/joe/test/autotype/submitrequest" xmlns:xsi="http://www.w
3.org/2001/XMLSchema-instance"><requestHeader xmlns="ca/joe/test/autotype/submit
request"><ns1:customerInteractionType
xmlns:ns1="ca/joe/test/autotype/testcommon request">ContactCentre</ns1:customerInteractionType><ns2:serviceRequestUserId xm
lns:ns2="ca/joe/test/autotype/testcommonrequest">N296922</ns2:serviceRequestUser Id><ns3:serviceConsumer
xmlns:ns3="ca/joe/test/autotype/testcommonrequest">Odorr max</ns3:serviceConsumer><ns4:serviceRequestTimestamp xmlns:ns4="ca/joe/test/aut
otype/testcommonrequest">2009-10-29T16:13:26.524-04:00</ns4:serviceRequestTimest amp><ns5:language
xmlns:ns5="ca/joe/test/autotype/testcommonrequest">English</ns 5:language><ns6:referenceID
xmlns:ns6="ca/joe/test/autotype/testcommonrequest">A 26P83Z3</ns6:referenceID></requestHeader></n1:submit>
This seems to work OK so it seems you just need to specify the namespace when you perform the select e.g.
select extract(xml_column,xml_path,namespace) from table;
Hope this helps.
Cheers,
Ian
|---------+----------------------------->
| | ganstadba_at_hotmail.|
| | com |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 03/11/2009 13:13 |
| | Please respond to |
| | ganstadba |
| | |
|---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: mark-clist_at_npsl.co.uk, oracle-l_at_freelists.org | | cc: | | Subject: RE: valid node xmlparser oracle 10g | >--------------------------------------------------------------------------------------------------------------|
Thanks for the reply. A piece of the xml looks like this, looking at the documentation, it looks like you can add the namespaces to the extract or extractvalue but I couldn't get it to work, kept getting errors. I think I'll fire up an 11G db and see what I can work with. It looks like extract and extractvalue are deprecated in 11G. This xml is just a fragment the whole xml is 126K.
<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>
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark Weaver
Sent: Monday, November 02, 2009 8:35 PM
To: oracle-l_at_freelists.org
Subject: Re: valid node xmlparser oracle 10g
On 02/11/2009 4:57 PM, Michael McMullen wrote:
> Does anyone know offhand if nodes like "n1:submit" is valid in xml. All > of our xml is changing that we parse to run reports on and our tests are > saying >
yes, they are valid -- they are called namespaces. There should be an xmlns attribute (usually at the start of the document, but it can be for individual elements), that declares the namespace, see:
http://www.w3.org/TR/REC-xml-names/
for the gory details. It might be simply that you are missing the namespace declaration -- you can usually provide these externally to parsers which is what the app that generates the data might be doing.
I've not checked that namespaces actually work with Oracle's xml parser, but I would think that they would as they are pretty fundamental.
-- 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. †Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^Received on Tue Nov 03 2009 - 08:51:19 CST