Re: help with xml

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Wed, 24 Sep 2008 09:39:01 +0100
Message-ID: <OF04D5265C.9AC71060-ON802574CE.002E927F-802574CE.002F84A1@ons.gsi.gov.uk>

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_at_CeDeT.dk      |

|         |           Sent by:          |

|         |           oracle-l-bounce_at_fr|

|         |           eelists.org       |

|         |                             |

|         |                             |

|         |           24/09/2008 08:16  |

|         |           Please respond to |

|         |           mgs               |

|         |                             |

|---------+----------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       oracle-l_at_freelists.org                                                                       |
  |       cc:       chris_stephens_at_admworld.com                                                                  |
  |       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_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-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.
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^

Received on Wed Sep 24 2008 - 03:39:01 CDT