Re: help with xml
Date: Wed, 24 Sep 2008 09:16:45 +0200
Message-ID: <20080924091645.f27iqkyksocwoks0@webmail.cedet.dk>
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_at_admworld.com>:
> 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.
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 24 2008 - 02:16:45 CDT