PL/SQL Challenge 每日一题：2018-1-25 XMLFOREST函数

newkid

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

以往旧题索引：
/forum.php?m ... eid&typeid=1808

原始出处：


作者： Kim Berg Hansen

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

我有一张保存着人名的表：

create table qz_people (
   id          integer primary key
, first_name  varchar2(20)
, middle_name varchar2(20)
, last_name   varchar2(20)
)
/

insert into qz_people values (42, 'Chuck', 'Edward', 'Brown')
/
insert into qz_people values (43, 'Alice', NULL    , 'Jones')
/
insert into qz_people values (44, 'Jenny', 'Senufo', 'Smith')
/
commit
/

对于每一行我需要输出一个名为People的XML元素，它含有这四个列的值作为XML元素。

如果三个名字列中的任何一个为NULL值，那么这个名字元素就不应该出现在XML中，即使作为空元素也不行。（对这些数据而言，Alice Jones 的XML不应该含有MiddleName元素，即使是<MiddleName/>这样的空格式也不行）

我写了这个未完成的查询，使用了XMLSERIALIZE来美化输出XML，这样我就能测试我的XML的生成：

select xmlserialize(
          document
          ##REPLACE##
          indent
       ) as people_xml
  from qz_people
order by id
/

哪些选项包含了一个XML表达式，可以用来取代##REPLACE##，使得查询返回这个输出：

PEOPLE_XML
----------------------------------------------------------------------
<People>
  <Id>42</Id>
  <FirstName>Chuck</FirstName>
  <MiddleName>Edward</MiddleName>
  <LastName>Brown</LastName>
</People>

<People>
  <Id>43</Id>
  <FirstName>Alice</FirstName>
  <LastName>Jones</LastName>
</People>

<People>
  <Id>44</Id>
  <FirstName>Jenny</FirstName>
  <MiddleName>Senufo</MiddleName>
  <LastName>Smith</LastName>
</People>

(A)
XMLELEMENT(
    "People"
  , XMLELEMENT("Id"        , id)
  , XMLELEMENT("FirstName" , first_name)
  , XMLELEMENT("MiddleName", middle_name)
  , XMLELEMENT("LastName"  , last_name)
)

(B)
XMLELEMENT(
    "People"
  , XMLELEMENT("Id", id)
  , NVL2(first_name , XMLELEMENT("FirstName" , first_name ), NULL)
  , NVL2(middle_name, XMLELEMENT("MiddleName", middle_name), NULL)
  , NVL2(last_name  , XMLELEMENT("LastName"  , last_name  ), NULL)
)

(C)
  XMLELEMENT(
       "People"
     , XMLFOREST(id          AS "Id")
     , XMLFOREST(first_name  AS "FirstName")
     , XMLFOREST(middle_name AS "MiddleName")
     , XMLFOREST(last_name   AS "LastName")
    )

(D)
XMLELEMENT(
    "People"
  , XMLFOREST(
       id          AS "Id"
     , first_name  AS "FirstName"
     , middle_name AS "MiddleName"
     , last_name   AS "LastName"
    )
)

(E)
XMLELEMENT(
    XMLFOREST(
       id          AS "Id"
     , first_name  AS "FirstName"
     , middle_name AS "MiddleName"
     , last_name   AS "LastName"
    ) AS "People"
)

(F)
XMLFOREST(
    XMLFOREST(
       id          AS "Id"
     , first_name  AS "FirstName"
     , middle_name AS "MiddleName"
     , last_name   AS "LastName"
    ) AS "People"
)

(G)
XMLFOREST(
    "People"
  , XMLFOREST(
       "Id"        , id
     , "FirstName" , first_name
     , "MiddleName", middle_name
     , "LastName"  , last_name
    )
)

liwenrongdba

正确答案：BCDF
A：错误，会显示<MiddleName/>。
B：正确，用NVL2函数先排除掉为空的值。
C：正确，XMLFOREST函数如果值为空，则不会创建xml元素。
D：正确，原理跟答案C相同。
E：错误，XMLELEMENT缺少标识，会报错，缺失逗号。（文档：You must specify a value for identifier, which Oracle Database uses as the enclosing tag）
F：正确。
G：错误，会报错，参数2（用于XMLFOREST函数）必须具有别名。

〇〇

xml在实际项目中没用过

newkid

答案 BCDF, 2楼得奖。

A: XMLELEMENT函数不会跳过那些为NULL的元素，相反，它会创建一个空元素，这是我们在输出中已经明确不想要的。这个选项会为Alice Jones创建一个空的MiddleName元素，得到这个错误输出：

PEOPLE_XML
----------------------------------------------------------------------
<People>
  <Id>42</Id>
  <FirstName>Chuck</FirstName>
  <MiddleName>Edward</MiddleName>
  <LastName>Brown</LastName>
</People>

<People>
  <Id>43</Id>
  <FirstName>Alice</FirstName>
  <MiddleName/>
  <LastName>Jones</LastName>
</People>

<People>
  <Id>44</Id>
  <FirstName>Jenny</FirstName>
  <MiddleName>Senufo</MiddleName>
  <LastName>Smith</LastName>
</People>

B: 我们可以自己测试NULL, 仅仅在非空值的时候创建元素，佛祖就用一个NULL（不存在）元素。这样我们就得到了正确的输出。
C: 如果不在people的不同字段上使用XMLELEMENT，我们也可以用XMLFOREST。XMLFOREST函数就是我们想要的——它在碰到NULL时会跳过元素而不是创建一个空元素。
D: XMLFOREST 还可以创建一个多元素的列表，如果我们给它多个参数的话。跳过NULL值元素的规则适用于所有元素。
E: "value AS identifier"这样的语法是XMLFOREST中的做法，但是XMLELEMENT总是先需要一个标识符，然后用逗号和它的值隔开。（在XMLELEMENT中，"AS"只是用于XMLATTRIBUTES，或者用于对象/集合类型的值）
此处我们试图将XMLFOREST风格的"AS"语法用于XMLELEMENT，这是不允许的，所以这个选项会报错：
ORA-00917: missing comma.

F: 正如我们在选项C和D中看到的，可以用一个单独的"value AS identifer"，也可以用多个这样的参数，所以我们可以完全不用XMLELEMENT，而用两个嵌套的XMLFOREST来完成。

G: 但是XMLFOREST需要"value AS identifier"语法。如果我们试图用XMLELEMENT风格的"identifier, value"，这个选项就会报错：
ORA-19208: parameter 2 of function XMLFOREST must be aliased.