ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 630|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2017-3-28 XML函数

[复制链接]
论坛徽章:
453
秀才
日期:2015-08-18 09:49:27举人
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 2017-4-13 04:40 | 显示全部楼层 |阅读模式

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

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

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

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

作者:        Kim Berg Hansen

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

有个外部系统会给我发带有XML的数据。这个系统在ORACLE中有一个 实体-属性-值 (entity-attribute-value, 也叫EAV)的模型,我会得到每个person实体的数据行,带有XML格式的属性-值对。它是用PIVOT XML, 构造出来的,为了模拟这个得到的数据,我在数据库中设置了一个测试环境:

create table qz_persons (
   id    integer primary key
, name  varchar2(10)
)
/

create table qz_attributes (
   id    integer primary key
, name  varchar2(10)
)
/

create table qz_values (
   person_id   integer references qz_persons
, attr_id     integer references qz_attributes
, attr_value  varchar2(10)
)
/

insert into qz_persons values (1, 'Jack')
/
insert into qz_persons values (2, 'Jill')
/

insert into qz_attributes values (10, 'Eyecolor')
/
insert into qz_attributes values (11, 'Haircolor')
/

insert into qz_values values (1, 10, 'Blue')
/
insert into qz_values values (1, 11, 'Blond')
/
insert into qz_values values (2, 10, 'Brown')
/
insert into qz_values values (2, 11, 'Black')
/

commit
/

create or replace view qz_persons_xml
as
select id
     , name
     , attr_name_xml
  from (
   select p.id
        , p.name
        , a.name as attr_name
        , v.attr_value
     from qz_persons p
     join qz_values v
         on v.person_id = p.id
     join qz_attributes a
         on a.id = v.attr_id
  )
pivot xml (
   max(attr_value) as attr_value
   for attr_name in (any)
)
/

select p.id
     , p.name
     , xmlserialize(document p.attr_name_xml indent) as attr_name_xml
  from qz_persons_xml p
order by id
/

ID NAME       ATTR_NAME_XML
--- ---------- -------------------------------------------------------
  1 Jack       <PivotSet>
                 <item>
                   <column name="ATTR_NAME">Eyecolor</column>
                   <column name="ATTR_VALUE">Blue</column>
                 </item>
                 <item>
                   <column name="ATTR_NAME">Haircolor</column>
                   <column name="ATTR_VALUE">Blond</column>
                 </item>
               </PivotSet>

  2 Jill       <PivotSet>
                 <item>
                   <column name="ATTR_NAME">Eyecolor</column>
                   <column name="ATTR_VALUE">Brown</column>
                 </item>
                 <item>
                   <column name="ATTR_NAME">Haircolor</column>
                   <column name="ATTR_VALUE">Black</column>
                 </item>
               </PivotSet>

视图的输出代表着我从外部系统得到的数据。但是我想像普通的关系列那样读取数据而不是属性-值对。

哪些选项包含的查询可以从视图读取数据并且给我这个输出:

ID NAME       EYECOLOR   HAIRCOLOR
--- ---------- ---------- ----------
  1 Jack       Blue       Blond
  2 Jill       Brown      Black
  

(A)
select p.id
     , p.name
     , extractvalue(
          p.attr_name_xml
        , '/PivotSet
              /item[column[@name = "ATTR_NAME"] = "Eyecolor"]
                 /column[@name = "ATTR_VALUE"]'
       ) as eyecolor
     , extractvalue(
          p.attr_name_xml
        , '/PivotSet
              /item[column[@name = "ATTR_NAME"] = "Haircolor"]
                 /column[@name = "ATTR_VALUE"]'
       ) as haircolor
  from qz_persons_xml p
order by p.id
/

(B)
select p.id
     , p.name
     , extractvalue(
          p.attr_name_xml
        , '/PivotSet
              /item[column = "Eyecolor"]
                 /column[@name = "ATTR_VALUE"]'
       ) as eyecolor
     , extractvalue(
          p.attr_name_xml
        , '/PivotSet
              /item[column = "Haircolor"]
                 /column[@name = "ATTR_VALUE"]'
       ) as haircolor
  from qz_persons_xml p
order by p.id
/

(C)
select p.id
     , p.name
     , extractvalue(
          p.attr_name_xml
        , '/PivotSet
              /item/column[@name = "Eyecolor"]
                 /column'
       ) as eyecolor
     , extractvalue(
          p.attr_name_xml
        , '/PivotSet
              /item/column[@name = "Haircolor"]
                 /column'
       ) as haircolor
  from qz_persons_xml p
order by p.id
/

(D)
select p.id
     , p.name
     , xmlquery(
          'for $i in /PivotSet/item
           where $i/column[@name = "ATTR_NAME"]//text() = "Eyecolor"
           return $i/column[@name = "ATTR_VALUE"]//text()'
          passing p.attr_name_xml
          returning content
       ).getstringval() as eyecolor
     , xmlquery(
          'for $i in /PivotSet/item
           where $i/column[@name = "ATTR_NAME"]//text() = "Haircolor"
           return $i/column[@name = "ATTR_VALUE"]//text()'
          passing p.attr_name_xml
          returning content
       ).getstringval() as haircolor
  from qz_persons_xml p
order by p.id
/

(E)
select p.id
     , p.name
     , xmlquery(
          'for $i in /PivotSet/item
           let $attr_col  := $i/column[@name = "ATTR_NAME"]
             , $value_col := $i/column[@name = "ATTR_VALUE"]
           where $attr_col//text() = "Eyecolor"
           return $value_col//text()'
          passing p.attr_name_xml
          returning content
       ).getstringval() as eyecolor
     , xmlquery(
          'for $i in /PivotSet/item
           let $attr_col  := $i/column[@name = "ATTR_NAME"]
             , $value_col := $i/column[@name = "ATTR_VALUE"]
           where $attr_col//text() = "Haircolor"
           return $value_col//text()'
          passing p.attr_name_xml
          returning content
       ).getstringval() as haircolor
  from qz_persons_xml p
order by p.id
/

(F)
select p.id
     , p.name
     , xmlquery(
          'for $i in /PivotSet/item
           let $attr_val  := $i/column/@name["ATTR_NAME"]//text()
             , $value_val := $i/column/@name["ATTR_VALUE"]//text()
           where $attr_val = "Eyecolor"
           return $value_val'
          passing p.attr_name_xml
          returning content
       ).getstringval() as eyecolor
     , xmlquery(
          'for $i in /PivotSet/item
           let $attr_val  := $i/column/@name["ATTR_NAME"]//text()
             , $value_val := $i/column/@name["ATTR_VALUE"]//text()
           where $attr_val = "Haircolor"
           return $value_val'
          passing p.attr_name_xml
          returning content
       ).getstringval() as haircolor
  from qz_persons_xml p
order by p.id
/


(G)
select id
     , name
     , eyecolor
     , haircolor
  from (
   select p.id
        , p.name
        , x.attr_name
        , x.attr_value
     from qz_persons_xml p
        , xmltable(
             '/PivotSet/item'
             passing p.attr_name_xml
             columns
                attr_name  varchar2(10)
                           path 'column[@name = "ATTR_NAME" ]'
              , attr_value varchar2(10)
                           path 'column[@name = "ATTR_VALUE"]'
          ) x
  )
pivot (
   max(attr_value)
   for attr_name in (
      'Eyecolor'  as eyecolor
    , 'Haircolor' as haircolor
   )
)
order by id
/
论坛徽章:
10
优秀写手
日期:2014-02-27 06:00:13处女座
日期:2016-03-10 09:03:26白羊座
日期:2015-10-09 16:42:50慢羊羊
日期:2015-06-15 21:49:18金牛座
日期:2015-07-16 15:34:37慢羊羊
日期:2015-05-11 18:44:14白羊座
日期:2015-10-10 15:01:392015年新春福章
日期:2015-03-06 11:58:392015年新春福章
日期:2015-03-04 14:53:16妮可·罗宾
日期:2017-04-06 10:06:19
发表于 2017-4-13 09:49 | 显示全部楼层
ABDEG
A,B 对extractvalue的正确写法;
C  没有/PivotSet/item/column/column路径节点,而且/PivotSet/item/column节点的name属性没有Eyecolor,Haircolor的;
D  使用for 对节点遍历的写法
E  for + let 结合写法;
F  对节点属性的非法引用 $i/column/@name  
G xmltable正常用法;

最近正在学xml,不理解是否正确
参考的https://www.w3.org/TR/xquery-30/

使用道具 举报

回复
论坛徽章:
215
秀才
日期:2016-03-01 09:57:08白羊座
日期:2015-11-01 17:52:47射手座
日期:2015-09-21 12:29:56双子座
日期:2015-09-12 11:55:41天蝎座
日期:2015-09-11 10:48:10双鱼座
日期:2015-09-02 10:48:47射手座
日期:2015-10-06 12:37:30白羊座
日期:2015-08-30 21:21:20水瓶座
日期:2015-08-26 17:36:07双子座
日期:2015-08-13 14:10:00
发表于 2017-4-13 11:20 | 显示全部楼层
ABDEG

A,B 用的 extractvalue 方法
C  /column 必须要指定属性,如/column[@name = "ATTR_VALUE"],否则不能提取到值
D,E 用 xmlquery 方法
F  $attr_val  需要写出 $attr_col//text()
G 用 xmltable 函数的方法

学习了,不过现在XML格式用得少了,现在都转向json格式了

使用道具 举报

回复
论坛徽章:
453
秀才
日期:2015-08-18 09:49:27举人
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2017-4-14 05:18 | 显示全部楼层
答案ABDEG, 3楼得奖。

A: 利用带谓词表达式(在方括号中)的XPATH表达式,我们说明了需要的是带有一个子元素"column"的"item"元素,这个子元素有一个"name" 属性,其值为"ATTR_NAME",并且column元素值分别为"Eyecolor" 和 "Haircolor"。找到 "Item"元素后,我们要求"column"子元素的值,它具有"name" 属性并且其值为 "ATTR_VALUE"
B: 此处我们在查找指定的"item"元素时走了捷径,说明了我们需要的是有一个 "column"子元素的"item"元素,该子元素分别有值"Eyecolor" 和 "Haircolor"。在我们的数据中,可能在其它大部分的情况下,这个方法都很好,但是如果某个人的"Eyecolor"的值刚好是字符串"Haircolor",那么就行不通了。更好的方法是如同A选项那样通过"name"属性上的谓词来指定所需的子列名。
C: 错在我们的方括号谓词放错了位置。是的,我们查找的列元素有一个"name"属性并且带有相应的值。但是我们要求的是所找到的"column"元素的子元素"column",这样是找不着的,返回的是NULL,因此查询给了这个错误输出:

ID NAME       EYECOLOR   HAIRCOLOR
--- ---------- ---------- ----------
  1 Jack
  2 Jill

D: 如果不用XPath 表达式,我们也可以用XQuery FLOWR 语法。"for"将会遍历所有项目,"where"只会保留哪些带有一个"column"孩子,它具有等于 "ATTR_NAME" 的"name"属性,并且文本值分别等于"Eyecolor" 和 "Haircolor"。然后"return"为此项目获得"column"孩子的文本值,它具有等于 "ATTR_NAME" 的"name"属性。XMLQUERY返回的是XMLTYPE,所以为了得到所需结果我们得用tostringval()。

E: 我们也可以用"let"来定义基于 $i 项目的变量,并且在 "where"和"return" 表达式中使用变量,而不需要冗长的"where" 和 "return"表达式。

F: 类似C选项,此处我们也把方括号放错了位置。在"let"表达式中,我们要求的是"name"属性的文本值,它也不能被按要求过滤。我们从XMLQUERY的调用得到了NULL,得到这个错误输出:

ID NAME       EYECOLOR   HAIRCOLOR
--- ---------- ---------- ----------
  1 Jack
  2 Jill

G: 不用将Eyecolor和haircolor分开提取,我们还可以利用简单的XPath表达式,用XMLTABLE来提取名称/值对,然后用普通的SQL PIVOT来得到所需输出。

使用道具 举报

回复

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

本版积分规则

久等啦!10张门票开启你的DTCC2017之旅~

2017中国数据库技术大会将于2017年5月11-13日如约而至,本届大会以“数据驱动•价值发现”为主题,共设定2大主场和21个技术专场,云集海内外120+位技术大牛,共同探讨Oracle、MySQL、NoSQL、云端数据库、区块链、深度学习等领域的前瞻性热点话题。
即日起,填写DTCC2017会前调查问卷,即有机会赢取价值2600元的大会门票1张!仅限10张!
----------------------------------------
活动截止时间:2017年5月5日统一公布

问卷入口>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表