如何使用Oracle获取表中XML空标记的伪值? [英] How to get dummy value for XML empty tag in table using Oracle?

问题描述

select a.test 
from demotable b
    ,XMLTable('//test' passing xmltype(b.xmlTagColumn) columns test varchar(10) path '.') a

b.xmlTagColumn具有xml内容. 如果b.xmlTagColumn具有<test></test>，那么我从上述语句中没有得到任何结果，甚至没有返回null.如何获取空标记的空值或伪值?

b.xmlTagColumn has xml content. If b.xmlTagColumn has <test></test> then I am not getting anything from above statement not even null. How can I get null or dummy value for empty tag?

推荐答案

left join ... on 1=1

select  a.test 

from                demotable b 

        left join   XMLTable
                    (
                        '//test' 
                        passing xmltype(b.xmlTagColumn) 
                        columns 
                            test varchar(10) path '.'
                    ) a 

        on         1=1
;        

---

演示

---

Demo

create table demotable (id int,xmlTagColumn varchar2(100));

insert into demotable (id,xmlTagColumn) values (1,'<X><test>123</test></X>');
insert into demotable (id,xmlTagColumn) values (2,'<Y><test>456</test></Y>');
insert into demotable (id,xmlTagColumn) values (3,'<Z><prod>123</prod></Z>');

---

select  b.id
       ,a.test 

from                demotable b 

        left join   XMLTable
                    (
                        '//test' 
                        passing xmltype(b.xmlTagColumn) 
                        columns 
                            test varchar(10) path '.'
                    ) a 

        on         1=1
;        

---

+----+--------+
| ID | TEST   |
+----+--------+
| 1  | 123    |
+----+--------+
| 2  | 456    |
+----+--------+
| 3  | (null) |
+----+--------+

这篇关于如何使用Oracle获取表中XML空标记的伪值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！