Re: help with xml

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Wed, 24 Sep 2008 09:54:14 +0100
Message-ID: <OFE385DBA6.B32CD7F9-ON802574CE.00305B3C-802574CE.0030F954@ons.gsi.gov.uk>

Sorry my original message got a bit garbled - heres a better exmaple;

create table hts_xml (clob_content clob);

insert into hts_xml values(
'<?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>
       <plateSection>


         <plateType>PlateType</plateType>

         <plateSectionName>Plate#2</plateSectionName>

         <plateReadTime>  9:49 AM  9/24/2008</plateReadTime>

         <instrumentInfo>Extra Test Row</instrumentInfo>

       </plateSection>
     </experimentSection>

 </microplateDoc>');

select
extractvalue(z.column_value,'/plateSection/plateSectionName/text()','xmlns="http://moleculardevices.com/microplateML"' ) plate_section_name,

extractvalue(z.column_value,'/plateSection/instrumentInfo/text()','xmlns="http://moleculardevices.com/microplateML"') instrument_info
from hts_xml,

table(xmlsequence(extract(xmltype(clob_content),'/microplateDoc/experimentSection/plateSection','xmlns="http://molecular devices.com/microplateML"'))) z;

PLATE_SECT INSTRUMENT_INFO

---------- ----------------------------------------

Plate#1 PLUS190PC ROM v1.21 Aug 18 2005 Plate#2 Extra Test Row

Hope this helps.

Cheers,

Ian

|---------+----------------------------->
|         |           chris_stephens_at_adm|
|         |           world.com         |
|         |           Sent by:          |
|         |           oracle-l-bounce_at_fr|
|         |           eelists.org       |
|         |                             |
|         |                             |
|         |           23/09/2008 21:51  |
|         |           Please respond to |
|         |           chris_stephens    |
|         |                             |
|---------+----------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       oracle-l_at_freelists.org                                                                       |
  |       cc:                                                                                                    |
  |       Subject:  help with xml                                                                                |
  >--------------------------------------------------------------------------------------------------------------|

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/plateType/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.

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.(See attached file: attjki3j.dat)

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.

--
http://www.freelists.org/webpage/oracle-l




application/octet-stream attachment: attjki3j.dat

Received on Wed Sep 24 2008 - 03:54:14 CDT