Extracting part of XMLType in PL/SQL because of repeating node inside another repeating mode

Question

I have a XMLType object and I want to extract opening times into table.

<workspace>
  <title>workspace1</title>
  <item>
    <day>1</day>
    <openingTime>8:00</openingTime>
    <closingTime>12:00</closingTime>
  </item>
  <item>
    <day>1</day>
    <openingTime>13:00</openingTime>
    <closingTime>18:00</closingTime>
  </item>
<workspace>
<workspace>
  <title>workspace2</title>
  <item>
    <day>1</day>
    <openingTime>9:00</openingTime>
    <closingTime>14:00</closingTime>
  </item>
  <item>
    <day>3</day>
    <openingTime>12:00</openingTime>
    <closingTime>16:00</closingTime>
  </item>
<workspace>

I would use something like:

SELECT ExtractValue(Value(p),'workspace/item/day/text()') as day
      ,ExtractValue(Value(p),'workspace/item/openingTime/text()') as open
      ,ExtractValue(Value(p),'workspace/item/closingTime/text()') as close
      FROM TABLE (XMLSequence(Extract(y,'workspace'))) p
      WHERE ExtractValue(Value(p),'/workspace/title/text()') LIKE 'workspace1';

where y is XMLType above. But that won't work, because it will still find more than one item node. I need to extract ALL element values for title workspace2 (values 1, 9:00, 14:00, 3, 12:00, 16:00). It would help if I could extract not only value, but whole part of XMLType. Any ideas?
Thanks, Michal

Answer 1

With some @ThinkJet tricks your query may look like this

with x as (
  select 
    xmltype('
       <workplaces>     
        <workspace>
          <title>workspace1</title>
          <item>
            <day>1</day>
            <openingTime>8:00</openingTime>
            <closingTime>12:00</closingTime>
          </item>
          <item>
            <day>1</day>
            <openingTime>13:00</openingTime>
            <closingTime>18:00</closingTime>
          </item>
        </workspace>
        <workspace>
          <title>workspace2</title>
          <item>
            <day>1</day>
            <openingTime>9:00</openingTime>
            <closingTime>14:00</closingTime>
          </item>
          <item>
            <day>3</day>
            <openingTime>12:00</openingTime>
            <closingTime>16:00</closingTime>
          </item>
        </workspace>
       </workplaces>     
  ') xfield
  from dual
)
SELECT "day", "openingTime", "closingTime"
FROM xmltable('$doc//workspace[title=$workspace_filter]/item'
              passing (select xfield from x) as "doc",
                      ('workspace1') as "workspace_filter"
               columns "openingTime" path '//openingTime',
                       "closingTime" path '//closingTime',
                       "day" path '//day')