甲骨文解析XML返回空白(Oracle Parsing XML returns blank)

我有一个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.

文章来源: Oracle Parsing XML returns blank