Re: help with xml
- From: Michael Garfield Sørensen <mgs@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 24 Sep 2008 09:16:45 +0200
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
Other related posts:
