Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

loop through xmltype using xmltable

MartijnkeJun 11 2015 — edited Jun 11 2015

Hi all,

I am trying to loop through XML but whatever I put behind PATH I always get no rows returned,

here is my SQL :

  select *

    from xmltable('*/text()' passing xmltype('<?xml version="1.0" encoding="utf-8"?>

<TaskResult>

  <Sample>

    <SampleId

      SampleId1="Reference"

      SampleId2="ACIN"

      SampleId3=""

      SampleId4=""

      SampleId5=""

      SampleId6=""

      SampleId1Description="ID 1"

      SampleId2Description="ID 2"

      SampleId3Description=""

      SampleId4Description=""

      SampleId5Description=""

      SampleId6Description="" />

    <Task

      Name="ACIN"

      InternalId="T1319"

      StartTime="2015-05-19T08:29:18"

      EndTime="2015-05-19T08:47:38"

      State="Completed" />

    <Method

      Name="IM1_AI"

      MethodId="ACIN"

      MethodInternalId="M37"

      Version="32"

      ReleaseState="Released"

      IsApproved="False" />

    <Product

      Name=""

      ProductId=""

      InternalId=""

      Version=""

      ReleaseState=""

      IsApproved="" />

    <Account

      LogonName="PredefinedUser"

      FullName="Administrator" />

    <Resultset

      Approved="False" />

    <ResultResult

      Name="R1"

      Title="IM1_AI_Result"

      Type="ResultResult"

      Value="0.447"

      Unit="mg/g KOH"

      InstrumentLastAdjustmentDate=""

      InstrumentLastCalibrationDate=""

      InstrumentName="T90"

      InstrumentSerialNr="B502477066"

      InstrumentTypeInfo="T90"

      InstrumentInternalId="I6" />

    <ResultResult

      Name="R2"

      Title="IM1_AI_Start_mV"

      Type="ResultResult"

      Value="975"

      Unit="mV"

      InstrumentLastAdjustmentDate=""

      InstrumentLastCalibrationDate=""

      InstrumentName="T90"

      InstrumentSerialNr="B502477066"

      InstrumentTypeInfo="T90"

      InstrumentInternalId="I6" />

  </Sample>

</TaskResult>')

COLUMNS

   alfalabel char(10) PATH '/TaskResult/Sample/SampleId/@SampleId1/text()')

Kr

Martin

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 9 2015
Added on Jun 11 2015
2 comments
590 views