I am trying to loop through XML but whatever I put behind PATH I always get no rows returned,
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()')