Oracle Parsing XML возвращает пустое значение

Question

У меня есть NCLOB столбец в моей базе данных, который имеет такую ​​структуру:

<Storage xmlns="http://tempuri.org/Storage.xsd">
  <TableInfo>
    <Name>Genotypes</Name> <------------------ I DON'T want to get this
    ...
  </TableInfo>
  <ColumnInfo>
    <ID>1</ID>
    <Name>cre</Name> <------------------------ I want to get this
    ...
  </ColumnInfo>
  <ColumnInfo>
    <ID>2</ID>
    <Name>Tph2</Name> <----------------------- I want to get this
    ...
  </ColumnInfo>
  <ColumnInfo>
    <ID>3</ID>
    <Name>New_Field</Name> <------------------ I want to get this
    ...
  </ColumnInfo>
  ...
</Storage>

Мне нужно, чтобы получить все значения под /Storage/ColumnInfo/Name путь, и я пробовал этот запрос (на основе ответа на этот вопрос), но я получаю пустой результат.

Попытка решения

SELECT ver.class_version, x.tag
FROM class_version ver
  LEFT JOIN XMLTABLE('/Storage/ColumnInfo'
    PASSING SYS.DBMS_XMLGEN.GETXMLTYPE(
      'SELECT t.dictionary_data
      FROM (
        SELECT v.class_def_id, v.class_version, v.dictionary_data, MAX(v.row_created) OVER (PARTITION BY v.class_def_id) AS max_date
        FROM class_version v
      ) t'
    )
    COLUMNS tag VARCHAR(256) PATH 'Name'
  ) x ON (1=1)
WHERE ver.class_def_id = 2672
  AND ver.class_version = '1.0.0.4'

Желаемый вывод

class_version       tag
-------------------------------------------------------------
1.0.0.4             cre
1.0.0.4             Tph2
1.0.0.4             New_Field

Фактический вывод

class_version       tag
-------------------------------------------------------------
1.0.0.4             NULL

---

Структура таблицы

Может помочь, если я предоставлю структуру таблицы, вот она:

id    class_def_id   class_version     dictionary_data (NCLOB)
------------------------------------------------------------------------
1000  2672           1.0.0.0           NULL
1001  2672           1.0.0.1           -- XML (as per above) --
1002  2672           1.0.0.2           -- XML (as per above) --
1003  2672           1.0.0.3           -- XML (as per above) --
1004  2672           1.0.0.4           -- XML (as per above) --
2001  1234           1.0.0.0           NULL
2002  1234           1.0.0.1           -- XML (as per above) --
2003  1234           1.0.0.2           -- XML (as per above) --

В основном я пытаюсь получить значения всех /Storage/ColumnInfo/Name узлы в dictionary_data столбец для каждого class_def_id - из последней версии.

Обновленный запрос

WITH markup AS (
  SELECT *
  FROM (
    SELECT v.class_def_id, v.class_version, XMLTYPE(v.dictionary_data) AS xmldata, v.row_created,
      MAX(v.row_created) OVER (PARTITION BY v.class_def_id) AS max_date
    FROM class_version v
    WHERE v.dictionary_data IS NOT NULL
  ) q
  WHERE q.row_created = q.max_date
)

SELECT x.tag
FROM markup m
  CROSS JOIN XMLTABLE(
    XMLNAMESPACES(DEFAULT 'http://tempuri.org/Storage.xsd'),
    'Storage/ColumnInfo'
    PASSING m.xmldata
    COLUMNS tag VARCHAR2(11) PATH 'Name'
  ) x

Answer 1

но я получаю пустой результат.

Это потому, что есть пространство имен по умолчанию. использование xmlnamespaces() функция для указания пространства имен (в данном случае по умолчанию).

with 
  t1 (xmldata) as(
    select xmltype(to_nclob('
<Storage xmlns="http://tempuri.org/Storage.xsd">
  <TableInfo>
    <Name>Genotypes</Name> 
  </TableInfo>
  <ColumnInfo>
    <ID>1</ID>
    <Name>cre</Name> 
  </ColumnInfo>
  <ColumnInfo>
    <ID>2</ID>
    <Name>Tph2</Name>
  </ColumnInfo>
  <ColumnInfo>
    <ID>3</ID>
    <Name>New_Field</Name> 
  </ColumnInfo>
</Storage>')) from dual
  )
 select q.colname
   from t1  t
        cross join xmltable(xmlnamespaces(default 'http://tempuri.org/Storage.xsd'),
                            'Storage/ColumnInfo'
                             passing t.xmldata
                             columns ColName  varchar2(11)  path 'Name'
                           ) q

Результат:

COLNAME    
-----------
cre        
Tph2       
New_Field  

3 rows selected.