Oracle Parsing XML返回空白 [英] Oracle Parsing XML returns blank

问题描述

我的数据库上有一个NCLOB列，其结构如下:

I have an NCLOB column on my database which has a structure like this:

<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路径下的所有值，并且我一直在尝试此查询(基于这个问题的答案)，但是我得到的结果是空白.

I need to get all the values under the /Storage/ColumnInfo/Name path and I've been trying this query (based on the answer on this question) but I'm getting a blank result.

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

---

表结构

如果我提供表的结构可能会有所帮助，如下所示:

---

Table Structure

It may help if I provide the structure of the table so here it is:

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) --

基本上，我试图从最新版本的 class_def_id-中获取每个dictionary_data列中所有/Storage/ColumnInfo/Name节点的值是.

Basically I'm trying to get the values of all the /Storage/ColumnInfo/Name nodes in the dictionary_data column for each class_def_id - from the latest version that is.

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

推荐答案

但是我得到的结果是空白的.

but I'm getting a blank result.

这是因为存在默认名称空间.使用xmlnamespaces()函数指定名称空间(在本例中为默认名称空间).

This is because there is a default namespace. Use xmlnamespaces() function to specify a namespace (in this case default one).

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返回空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！