查看: 403|回复: 2

[每日一题] PL/SQL Challenge 每日一题:2019-8-8 XML函数中的EVALNAME

[复制链接]
论坛徽章:
535
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2019-8-13 03:30 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:Kim Berg Hansen

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我有一张表保存着我们的会议发言人,存有他们的名字和他们获得奖项(如果有获奖的话):

create table qz_speakers (
   id    integer primary key
, name  varchar2(20)
, award varchar2(20)
);

insert into qz_speakers values (
   40, 'Danilo Danutella', 'DeuceAssociate'
);
insert into qz_speakers values (
   41, 'Diablo Doritos'  , 'Deuce'
);
insert into qz_speakers values (
   42, 'Django Derringer', 'DeuceDirector'
);
insert into qz_speakers values (
   43, 'Double Dulcimer' , null
);

commit;

为了在我们的会议应用中使用,我需要将这个数据导出为XML。获奖头衔(如果有的话)应该被导出为 <Speaker> 这个XML元素的一个属性,使得如果一个发言人有获奖的话,其属性名称应该等于获奖头衔,而其值应该等于'true'。如果一个发言人没有获奖,则这个属性不应该存在。

为此我写了一个未完成的查询:

select xmlserialize(content speaker_xml indent) as speaker_xml_string
  from (
   select xmlelement(
             "Speakers"
           , xmlagg(
                ##REPLACE##
                order by id
             )
          ) as speaker_xml
     from qz_speakers
  );

那些选项包含了一个表达式可以用来创建<Speaker> XML元素,取代##REPLACE##之后使得查询返回这个输出:

SPEAKER_XML_STRING
------------------------------------------------------------
<Speakers>
  <Speaker DeuceAssociate="true">Danilo Danutella</Speaker>
  <Speaker Deuce="true">Diablo Doritos</Speaker>
  <Speaker DeuceDirector="true">Django Derringer</Speaker>
  <Speaker>Double Dulcimer</Speaker>
</Speakers>

注意:输出采用了这个格式设置:

set long 4000
column speaker_xml_string format a60

(A)
xmlelement(
   "Speaker"
, xmlattributes('true' as award)
, name
)

(B)
xmlelement(
   "Speaker"
, xmlattributes('true' as evalname award)
, name
)

(C)
xmlelement(
   "Speaker"
, xmlattributes(nvl2(award, 'true', null) as evalname award)
, name
)

(D)
xmlelement(
   "Speaker"
, nvl2(award, xmlattributes('true' as evalname award), null)
, name
)

(E)
   '<Speaker'
|| nvl2(award, ' ' || award || '="true"', null)
|| '>' || name || '</Speaker>'

(F)
xmlparse(
   content
      '<Speaker'
   || nvl2(award, ' ' || award || '="true"', null)
   || '>' || name || '</Speaker>'
)

(G)
xmltype(
      '<Speaker'
   || nvl2(award, ' ' || award || '="true"', null)
   || '>' || name || '</Speaker>'
)

论坛徽章:
466
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2019-8-13 11:01 | 显示全部楼层
答案:CFG
A: xmlattributes('true' as award) 产生的输出是这样的<Speaker AWARD="true"
B: 纠正A,xmlattributes('true' as evalname award), 输出的就是字段award的值作属性名,本来是对的,但函数xmlattributes好像不能直接处理空值,所以报错
C: 用了一个NVL2函数,如果字段award的值为NULL,那么xmlattributes(nvl2(award, 'true', null) as evalname award)输出为NULL
D: 相较于C,这里NVL2函数用在了xmlattributes的外面,由于要与外面的生成XML元素的函数xmlelement配合使用,这里是xmlelement第二个可选参数,是接xmlattribute属性子句的
E: 相较于C,这里xmlelement没有使用,直接字符连接,但这样不能被外层的xmlagg 函数支持
F: XMLParse函数解析一个包含XML数据的字符串并构造一个相应的XMLType实例,指定CONTENT后面的表达式的值就只是一个XML片段,如果式DOCUMENT,就是XML文档的根节点
G: XML数据的字符串直接构造一个相应的XMLType实例

使用道具 举报

回复
论坛徽章:
535
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2019-8-14 05:03 | 显示全部楼层
答案CFG, 2楼得奖。

A: 在此处的XMLATTRIBUTES子句中,字面量'true'是属性的值,AWARD是一个标识符,会当作属性的名称,因此这里的AWARD不是表的列AWARD,而是一个别名,会给出这个错误输出:

SPEAKER_XML_STRING
------------------------------------------------------------
<Speakers>
  <Speaker AWARD="true">Danilo Danutella</Speaker>
  <Speaker AWARD="true">Diablo Doritos</Speaker>
  <Speaker AWARD="true">Django Derringer</Speaker>
  <Speaker AWARD="true">Double Dulcimer</Speaker>
</Speakers>

B: 通过使用EVALNAME,AWARD不再是一个标识符别名,而是表的列AWARD,它的值会被求出,并且用作属性名称。这几乎已经就是我们想要的,但是最后一个发言人碰到了问题,他的AWARD值为NULL, 对他来说起始标记就是 <Speaker ="true">,这在XML中是无效的,会报错:
LPX-00240: element-start tag is not well formed.

C: 对前一选项的问题有个显而易见的解决办法,就是利用诸如EVALNAME NVL(AWARD, 'NoAward')这样的,它会使得最后一个发言人的XML标记变成 <Speaker NoAward="true">. 如果应用是这样设计的话就没问题,但是我们的要求说的是XML里不要有属性。因此我们在XMLATTRIBUTES子句的值用了NVL2,使得我们在发言人有获奖的情况下使用'true'这个值,在没有获奖的情况下则用NULL。当NULL被传递给XMLATTRIBUTES,没有任何属性会被创建,我们得到了所需的输出。

D: 我们不能这样使用NVL2来返回一整个XMLATTRIBUTES子句,或者不返回任何东西。这是不被支持的,会报错:
ORA-00907: missing right parenthesis.

E: 我们可以手工将XML创建为文本而不是使用XMLELEMENT/XMLATTRIBUTES, 但是这个选项的文本会被作为传给XMLAGG函数的参数,这个函数期待的是一个XMLTYPE参数而不是VARCHAR2,所以会报错:
PLS-306: wrong number or types of arguments in call to 'SYS_IXMLAGG'.

F: 为了修复前一选项,我们可以利用XMLPARSE之类的方法将字符串转换成XMLTYPE。

G: 或者不用XMLPARSE,用XMLTYPE本身的构建函数,也能得到相同结果。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表