Oracle Parsing XML returns empty

Question

I have an NCLOB column in my database that has this structure:

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

I need to get all the values ​​in the path /Storage/ColumnInfo/Name , and I tried this query (based on the answer to this question ), but I'm getting an empty result.

Attempt to solve

 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' 

Desired output

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

Actual output

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

---

Table structure

This may help if I provide a table structure, 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) -- 

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

Updated request

 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

but I get an empty result.

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

 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 

Result:

 COLNAME ----------- cre Tph2 New_Field 3 rows selected.