我有一个 xmltype table 如下:
SQL> select object_value from tbl_testxml;
<members>
<grandfather name="Tom" age="66">
<father name="John" age="46">
<son name="Simon" age="20"/>
<son name="Steve" age="19"/>
</father>
<father name="Jeff" age="45">
<son name="Mark" age="17"/>
<son name="Mathiew" age="15"/>
</father>
</grandfather>
</members>我想用sql抽取其中的祖父名字,父亲名字,儿子名字,如果是一家人的话输出在一行上,于是我用了这个sql:select extract(value(v_xml),'//grandfather/@name') as grandfather,
extract(value(v_xml),'//grandfather/father/@name') as father,
extract(value(v_xml),'//grandfather/father/son/@name') as son
from tbl_testxml t,
TABLE (XMLSEQUENCE (EXTRACT (t.object_value, '/members'))) v_xml;结果得到输出如下:grandfather father son
------------------ --------------------- --------------------------------------
Tom JohnJeff SimonSteveMarkMathiew可是这个结果不是我想要的,我想要输出成这样:grandfather father son
-------------- --------- -------------
Tom John Simon
Tom John Steve
Tom Jeff Mark
Tom Jeff Mathiew试了 xmlquery, xmltable 都不行, 整个项目就卡在这里了.由于刚刚接触oracle xmldb,经验不足请各位大侠支招.谢谢了.
SQL> select object_value from tbl_testxml;
<members>
<grandfather name="Tom" age="66">
<father name="John" age="46">
<son name="Simon" age="20"/>
<son name="Steve" age="19"/>
</father>
<father name="Jeff" age="45">
<son name="Mark" age="17"/>
<son name="Mathiew" age="15"/>
</father>
</grandfather>
</members>我想用sql抽取其中的祖父名字,父亲名字,儿子名字,如果是一家人的话输出在一行上,于是我用了这个sql:select extract(value(v_xml),'//grandfather/@name') as grandfather,
extract(value(v_xml),'//grandfather/father/@name') as father,
extract(value(v_xml),'//grandfather/father/son/@name') as son
from tbl_testxml t,
TABLE (XMLSEQUENCE (EXTRACT (t.object_value, '/members'))) v_xml;结果得到输出如下:grandfather father son
------------------ --------------------- --------------------------------------
Tom JohnJeff SimonSteveMarkMathiew可是这个结果不是我想要的,我想要输出成这样:grandfather father son
-------------- --------- -------------
Tom John Simon
Tom John Steve
Tom Jeff Mark
Tom Jeff Mathiew试了 xmlquery, xmltable 都不行, 整个项目就卡在这里了.由于刚刚接触oracle xmldb,经验不足请各位大侠支招.谢谢了.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货