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屋!