Re: help with xml
- From: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
- To: mgs@xxxxxxxx
- Date: Wed, 24 Sep 2008 09:39:01 +0100
Hi Chris,
You need to add the namespace as an extra parameter to the extract method
e.g.
select
extractvalue(xmltype(x),'/microplateDoc/experimentSection/plateSection/plateType/text()','xmlns="http://moleculardevices.com/microplateML";')
from test_xml;
EXTRACT(XMLTYPE(X),'/MICROPLATEDOC/EXPERIMENTSECTION/PLATESECTION/PLATETYPE/TEXT
--------------------------------------------------------------------------------
PlateType
If there were mulitple Plate Types you need to use the xmlsequence function
to create a table of values somthing like
select extract(z.column_value,'/plateSection/plateType/text()')
|---------+----------------------------->
| | mgs@xxxxxxxx |
| | Sent by: |
| | oracle-l-bounce@fr|
| | eelists.org |
| | |
| | |
| | 24/09/2008 08:16 |
| | Please respond to |
| | mgs |
| | |
|---------+----------------------------->
>--------------------------------------------------------------------------------------------------------------|
|
|
| To: oracle-l@xxxxxxxxxxxxx
|
| cc: chris_stephens@xxxxxxxxxxxx
|
| Subject: Re: help with xml
|
>--------------------------------------------------------------------------------------------------------------|
Hi Chris,
Seems as if the namespace thingy
(xmlns="http://moleculardevices.com/microplateML";) is getting in your
way:
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL>
SQL> SELECT
2 extractValue(
3 XMLType(
4 '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
5 <microplateDoc xmlns="http://moleculardevices.com/microplateML";>
6 <fileVersion>2.0.7</fileVersion>
7 <experimentSection sectionName="Experiment#1">
8 <plateSection>
9 <plateType>PlateType</plateType>
10 <plateSectionName>Plate#1</plateSectionName>
11 <plateReadTime> 2:10 PM 8/27/2008 </plateReadTime>
12 <instrumentInfo>PLUS190PC ROM v1.21 Aug 18
2005</instrumentInfo>
13 </plateSection>
14 </experimentSection>
15 </microplateDoc>')
16
,'/microplateDoc/experimentSection/plateSection/plateSectionName') AS
pSN
17 FROM dual;
PSN
--------------------------------------------------------------------------------
SQL>
SQL> SELECT
2 extractValue(
3 XMLType(
4 '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
5 <microplateDoc>
6 <fileVersion>2.0.7</fileVersion>
7 <experimentSection sectionName="Experiment#1">
8 <plateSection>
9 <plateType>PlateType</plateType>
10 <plateSectionName>Plate#1</plateSectionName>
11 <plateReadTime> 2:10 PM 8/27/2008 </plateReadTime>
12 <instrumentInfo>PLUS190PC ROM v1.21 Aug 18
2005</instrumentInfo>
13 </plateSection>
14 </experimentSection>
15 </microplateDoc>')
16
,'/microplateDoc/experimentSection/plateSection/plateSectionName') AS
pSN
17 FROM dual;
PSN
--------------------------------------------------------------------------------
Plate#1
Don't no why, but HTH
Michael
Quoting "Stephens, Chris" <chris_stephens@xxxxxxxxxxxx>:
> Can someone help me with the following:
>
>
>
> I have a table:
>
>
>
> CREATE TABLE "HTS"."HTS_XML2"
>
> ( "ID" NUMBER,
>
> "NAME" VARCHAR2(90 BYTE),
>
> "FILENAME" VARCHAR2(400 BYTE),
>
> "MIME_TYPE" VARCHAR2(48 BYTE),
>
> "UPLOADED_BY" VARCHAR2(100 BYTE),
>
> "CLOB_CONTENT" "HTS"."XMLTYPE"
>
> )
>
>
>
> In that table I have 1 row. Clob_content contains an xml file:
>
>
>
> <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
>
> <microplateDoc xmlns="http://moleculardevices.com/microplateML";>
>
>
>
> <fileVersion>2.0.7</fileVersion>
>
>
>
> <experimentSection sectionName="Experiment#1">
>
> <plateSection>
>
> <plateType>PlateType</plateType>
>
> <plateSectionName>Plate#1</plateSectionName>
>
> <plateReadTime> 2:10 PM 8/27/2008
>
> </plateReadTime>
>
> <instrumentInfo>PLUS190PC ROM v1.21 Aug 18 2005</instrumentInfo>
>
> </plateSection>
>
> </experimentSection>
>
>
>
> </microplateDoc>
>
>
>
> I am trying to pull out the plateType value with:
>
>
>
> select
> extract(clob_content,'/microplateDoc/experimentSection/plateSection/plat
> eType/text()')
>
> from hts_xml2;
>
>
>
> or
>
>
>
> select
> extractvalue(clob_content,'/microplateDoc/experimentSection/plateSection
> /plateType')
>
> from hts_xml2;
>
>
>
> or any other iteration I've been able to think of.
>
>
>
> How do I get at that value and if <plateType> occurs more than once in
> the file how do I extract all occurrences of that value?
>
>
>
> Thanks,
>
> Chris
>
>
> CONFIDENTIALITY NOTICE:
> This message is intended for the use of the individual or
entity to
> which it is addressed and may contain information that is privileged,
> confidential and exempt from disclosure under applicable law. If
> the reader of this message is not the intended recipient or the
> employee or agent responsible for delivering this message to the
> intended recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly
> prohibited. If you have received this
> communication in error, please notify us immediately by email reply.
>
>
>
>
--
//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 2007/11/0032.) 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 2007/11/0032.) 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.
Other related posts:
