Думаю можно без комментариев.
Запрос:WITH
q AS (SELECT xmltype('
<xml>
<head>
<valueA>A</valueA>
<valueB>B</valueB>
</head>
<repeat1>
<valueC>C one</valueC>
<valueD>D one</valueD>
</repeat1>
<repeat1>
<valueC>C two</valueC>
<valueD>D two</valueD>
</repeat1>
<repeat1>
<valueC>C three</valueC>
<valueD>D three</valueD>
</repeat1>
</xml>
') XML FROM dual)
SELECT extractvalue(XML, '//xml/head/valueA') valueA
, extractvalue(XML, '//xml/head/valueB') valueB
, repeat1.valueC, repeat1.valueD
FROM q
, XMLTable('for $i in //xml/repeat1 return $i'
PASSING q.XML
COLUMNS valueC VARCHAR2(250) path '//repeat1/valueC'
, valueD VARCHAR2(250) path '//repeat1/valueD'
) repeat1Результат:
