查看: 3988|回复: 28

[SQL] 【讨论】求思路,XML转关系型SQL

[复制链接]
论坛徽章:
17
2013年新春福章
日期:2013-02-25 14:51:242015年新春福章
日期:2015-03-04 14:53:16马上加薪
日期:2014-12-18 01:09:30祖国65周年纪念徽章
日期:2014-09-27 08:49:22马上有房
日期:2014-07-16 11:59:32优秀写手
日期:2014-07-10 06:00:12马上加薪
日期:2014-04-20 23:04:51马上有对象
日期:2014-04-19 23:32:33马上有车
日期:2014-03-29 23:05:24问答徽章
日期:2014-01-17 15:19:03
发表于 2014-7-3 02:56 | 显示全部楼层 |阅读模式
解析的XML文件,把它解析成关系型table:
  1. <?xml version="1.0" encoding="utf-8"?>
  2. <client name="iphone" version="2.5.1.0" versiontype="0000">
  3. <user account="18888888888">
  4.     <events count_date="2012-08-19">
  5.         <event network="WiFi" count="5" durations="20">time_reg</event>
  6.         <event network="WiFi" count="9">send_soundim</event>
  7.     </events>
  8.     <events count_date="2012-08-18">
  9.         <event network="WiFi" count="4">send_sys_expression</event>
  10.         <event network="WiFi" count="5">click_accessory</event>  
  11.     </events>
  12. </user >
  13. <user account=" noaccount">
  14.     <events count_date="2012-08-19">
  15.         <event network="CMCC3G" count="2">click_reg_next</event>
  16.     </events>
  17. </user >
  18. </client>
复制代码
  1. CREATE TABLE client_parse_xml(
  2.     client VARCHAR2(20),  
  3.     version VARCHAR2(24),  
  4.     versiontype VARCHAR2(16),  
  5.     account VARCHAR2(50),  
  6.     network VARCHAR2(20),  
  7.     action VARCHAR2(50),  
  8.     count_date VARCHAR2(20),  
  9.     cnt INT,  
  10.     duration INT);
复制代码


以为很多元素都是相同的很难定位,比方events和event,188888888888和noaccount都有,是同名的,Xpath在oracle不能指向父节点,感觉所以不能用循环
  1. select   t.name,
  2.    t.version,
  3.    t.versionType,
  4.    t.account,  
  5.    t.network,
  6.    t.action,
  7.    t.count_date,
  8.    t.cnt,
  9.    t.duration
  10. from
  11. (SELECT XMLQuery('//client' PASSING res  RETURNING CONTENT) OBJECT_VALUE
  12. FROM xdb.resource_view r where any_path like '%client.xml'
  13. and under_path(RES, '/home/SCOTT/demonstrations/introduction') = 1)p,
  14. XMLTable('for $i in /client/user/events/event
  15.               for $j in /client
  16.                 for $k in /client/user
  17.                   for $l in /client/user/events
  18.               return($i,$j,$k,$l)      
  19. ' PASSING p.OBJECT_VALUE
  20.           COLUMNS name          VARCHAR2(20)      PATH '@name',   --- PATH 里 上面循环$l已进入子路径,元素就不能指向父节点,不支持父节点 ../ 或者全路径 /client/@name
  21.                   version          VARCHAR2(24)   PATH '@version',
  22.                   versionType      VARCHAR2(16)   PATH '@versiontype',
  23.                   account          VARCHAR2(50)   PATH '@account',
  24.                   network          VARCHAR2(20)   PATH '@network',
  25.                   duration         number(10)     PATH '@durations',
  26.                  action           VARCHAR2(50)    PATH 'text()',
  27.                 count_date       VARCHAR2(48)     PATH '@count_date',
  28.                   cnt              number(10)     PATH '@count'

  29. ) t
复制代码
结果,有120条:
  
NAMEVERSIONVERSIONTYPEACCOUNTNETWORKACTIONCOUNT_DATECNTDURATION




WiFitime_reg
5
20
iphone2.5.1.00000








18888888888










2012-08-19





WiFitime_reg
5
20
iphone2.5.1.00000








18888888888










2012-08-18





WiFitime_reg
5
20
iphone2.5.1.00000








18888888888










2012-08-19





WiFitime_reg
5
20
iphone2.5.1.00000








noaccount










2012-08-19





WiFitime_reg
5
20
iphone2.5.1.00000








noaccount










2012-08-18





WiFitime_reg
5
20
iphone2.5.1.00000








noaccount










2012-08-19





WiFisend_soundim
9

iphone2.5.1.00000








18888888888










2012-08-19





WiFisend_soundim
9

iphone2.5.1.00000








18888888888










2012-08-18





WiFisend_soundim
9

iphone2.5.1.00000








18888888888










2012-08-19





WiFisend_soundim
9

iphone2.5.1.00000








noaccount










2012-08-19





WiFisend_soundim
9

iphone2.5.1.00000








noaccount










2012-08-18





WiFisend_soundim
9

iphone2.5.1.00000








noaccount










2012-08-19





WiFisend_sys_expression
4

iphone2.5.1.00000








18888888888










2012-08-19





WiFisend_sys_expression
4

iphone2.5.1.00000








18888888888










2012-08-18





WiFisend_sys_expression
4

iphone2.5.1.00000








18888888888










2012-08-19





WiFisend_sys_expression
4

iphone2.5.1.00000








noaccount










2012-08-19





WiFisend_sys_expression
4

iphone2.5.1.00000








noaccount










2012-08-18





WiFisend_sys_expression
4

iphone2.5.1.00000








noaccount










2012-08-19





WiFiclick_accessory
5

iphone2.5.1.00000








18888888888










2012-08-19





WiFiclick_accessory
5

iphone2.5.1.00000








18888888888










2012-08-18





WiFiclick_accessory
5

iphone2.5.1.00000








18888888888










2012-08-19





WiFiclick_accessory
5

iphone2.5.1.00000








noaccount










2012-08-19





WiFiclick_accessory
5

iphone2.5.1.00000








noaccount










2012-08-18





WiFiclick_accessory
5

iphone2.5.1.00000








noaccount










2012-08-19





CMCC3Gclick_reg_next
2

iphone2.5.1.00000








18888888888










2012-08-19





CMCC3Gclick_reg_next
2

iphone2.5.1.00000








18888888888










2012-08-18





CMCC3Gclick_reg_next
2

iphone2.5.1.00000








18888888888










2012-08-19





CMCC3Gclick_reg_next
2

iphone2.5.1.00000








noaccount










2012-08-19





CMCC3Gclick_reg_next
2

iphone2.5.1.00000








noaccount










2012-08-18





CMCC3Gclick_reg_next
2

iphone2.5.1.00000








noaccount










2012-08-19


刚开始我想的是把他们每4条合并成一条,上面
1-4 条 合并到 1 成一条
5-9 条 合并到 5 成一条
用MODEL实现后,发现了错误,有20条
  1. SELECT NAME,VERSION,versiontype,ACCOUNT,NETWORK,action,count_date,cnt,duration FROM (SELECT rn,NAME,VERSION,versiontype,ACCOUNT,NETWORK,action,count_date,cnt,duration FROM (
  2. select   t.name,
  3.    t.version,
  4.    t.versionType,
  5.    t.account,  
  6.    t.network,
  7.    t.action,
  8.    t.count_date,
  9.    t.cnt,
  10.    t.duration
  11. from
  12. (SELECT XMLQuery('//client' PASSING res  RETURNING CONTENT) OBJECT_VALUE
  13. FROM xdb.resource_view r where any_path like '%client.xml'
  14. and under_path(RES, '/home/SCOTT/demonstrations/introduction') = 1)p,
  15. XMLTable('for $i in /client/user/events/event
  16.               for $j in /client
  17.                 for $k in /client/user
  18.                   for $l in /client/user/events
  19.               return($i,$j,$k,$l)      
  20. ' PASSING p.OBJECT_VALUE
  21.           COLUMNS name          VARCHAR2(20)      PATH '@name',
  22.                   version          VARCHAR2(24)   PATH '@version',
  23.                   versionType      VARCHAR2(16)   PATH '@versiontype',
  24.                   account          VARCHAR2(50)   PATH '@account',
  25.                   network          VARCHAR2(20)   PATH '@network',
  26.                   duration         number(10)     PATH '@durations',
  27.                  action           VARCHAR2(50)    PATH 'text()',
  28.                 count_date       VARCHAR2(48)     PATH '@count_date',
  29.                   cnt              number(10)     PATH '@count'

  30. ) t)
  31. MODEL RETURN UPDATED ROWS
  32. DIMENSION BY(ROWNUM AS rn)
  33. measures(NAME,VERSION,versiontype,ACCOUNT,NETWORK,action,count_date,cnt,duration)
  34. IGNORE NAV
  35. RULES AUTOMATIC ORDER
  36. (
  37. NAME[FOR rn FROM 1 TO 120 INCREMENT 4]=concat(NAME[CURRENTV()+1],NAME[CURRENTV()+2]),
  38. VERSION[FOR rn FROM 1 TO 120 INCREMENT 4]=concat(VERSION[CURRENTV()+1],VERSION[CURRENTV()+2]),
  39. versiontype[FOR rn FROM 1 TO 120 INCREMENT 4]=concat(versiontype[CURRENTV()+1],versiontype[CURRENTV()+2]),
  40. ACCOUNT[FOR rn FROM 1 TO 120 INCREMENT 4]=concat(ACCOUNT[CURRENTV()+1],ACCOUNT[CURRENTV()+2]),
  41. count_date[FOR rn FROM 1 TO 120 INCREMENT 4]=concat(count_date[CURRENTV()+2],count_date[CURRENTV()+3])
  42. )
  43. )
  44. GROUP BY NAME,VERSION,versiontype,ACCOUNT,NETWORK,action,count_date,cnt,duration ORDER BY ACCOUNT DESC,count_date;
复制代码
结果是,对比上面的XML文件,红色部分都是多出来的,因为循环,怎么去除掉呢,现在只有两个用户,多的话很麻烦,手动处理几乎不可能?
  
   NAMEVERSIONVERSIONTYPEACCOUNTNETWORKACTIONCOUNT_DATECNTDURATION
1iphone2.5.1.0000018888888888CMCC3Gclick_reg_next2012-08-18
2

2iphone2.5.1.0000018888888888WiFiclick_accessory2012-08-18
5

3iphone2.5.1.0000018888888888WiFisend_soundim2012-08-18
9

4iphone2.5.1.0000018888888888WiFisend_sys_expression2012-08-18
4

5iphone2.5.1.0000018888888888WiFitime_reg2012-08-18
5
20
6iphone2.5.1.0000018888888888CMCC3Gclick_reg_next2012-08-19
2

7iphone2.5.1.0000018888888888WiFiclick_accessory2012-08-19
5

8iphone2.5.1.0000018888888888WiFisend_soundim2012-08-19
9

9iphone2.5.1.0000018888888888WiFisend_sys_expression2012-08-19
4

10iphone2.5.1.0000018888888888WiFitime_reg2012-08-19
5
20
11iphone2.5.1.00000 noaccountCMCC3Gclick_reg_next2012-08-18
2

12iphone2.5.1.00000 noaccountWiFiclick_accessory2012-08-18
5

13iphone2.5.1.00000 noaccountWiFisend_soundim2012-08-18
9

14iphone2.5.1.00000 noaccountWiFisend_sys_expression2012-08-18
4

15iphone2.5.1.00000 noaccountWiFitime_reg2012-08-18
5
20
16iphone2.5.1.00000 noaccountCMCC3Gclick_reg_next2012-08-19
2

17iphone2.5.1.00000 noaccountWiFiclick_accessory2012-08-19
5

18iphone2.5.1.00000 noaccountWiFisend_soundim2012-08-19
9

19iphone2.5.1.00000 noaccountWiFisend_sys_expression2012-08-19
4

20iphone2.5.1.00000 noaccountWiFitime_reg2012-08-19
5
20




论坛徽章:
530
奥运会纪念徽章:垒球
日期: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
发表于 2014-7-3 03:25 | 显示全部楼层
time_reg不是8-19才有的吗?为什么你的结果里要在8-18也放一条?

使用道具 举报

回复
论坛徽章:
17
2013年新春福章
日期:2013-02-25 14:51:242015年新春福章
日期:2015-03-04 14:53:16马上加薪
日期:2014-12-18 01:09:30祖国65周年纪念徽章
日期:2014-09-27 08:49:22马上有房
日期:2014-07-16 11:59:32优秀写手
日期:2014-07-10 06:00:12马上加薪
日期:2014-04-20 23:04:51马上有对象
日期:2014-04-19 23:32:33马上有车
日期:2014-03-29 23:05:24问答徽章
日期:2014-01-17 15:19:03
 楼主| 发表于 2014-7-3 06:08 | 显示全部楼层
newkid 发表于 2014-7-3 03:25
time_reg不是8-19才有的吗?为什么你的结果里要在8-18也放一条?

恩, 没编辑好,是的,newkid已经改了

使用道具 举报

回复
求职 : 数据库管理员
招聘 : Java研发
认证徽章
论坛徽章:
6348
ITPUB9周年纪念徽章
日期:2014-05-02 10:36:402011新春纪念徽章
日期:2014-12-29 12:11:142010广州亚运会纪念徽章:卡巴迪
日期:2014-08-06 08:44:252012新春纪念徽章
日期:2014-12-29 12:11:142013年新春福章
日期:2014-12-29 12:11:14马上有车
日期:2014-12-29 12:11:14马上有房
日期:2014-12-29 12:11:14马上有钱
日期:2014-12-29 12:11:14马上有对象
日期:2014-12-29 12:11:14马上加薪
日期:2014-12-29 12:11:14
发表于 2014-7-3 08:19 | 显示全部楼层
你说说,你第一个XML 文件要解析成什么样结果的table

使用道具 举报

回复
论坛徽章:
17
2013年新春福章
日期:2013-02-25 14:51:242015年新春福章
日期:2015-03-04 14:53:16马上加薪
日期:2014-12-18 01:09:30祖国65周年纪念徽章
日期:2014-09-27 08:49:22马上有房
日期:2014-07-16 11:59:32优秀写手
日期:2014-07-10 06:00:12马上加薪
日期:2014-04-20 23:04:51马上有对象
日期:2014-04-19 23:32:33马上有车
日期:2014-03-29 23:05:24问答徽章
日期:2014-01-17 15:19:03
 楼主| 发表于 2014-7-3 09:22 | 显示全部楼层
本帖最后由 AnddyHua 于 2014-7-3 09:23 编辑
2009532140 发表于 2014-7-3 08:19
你说说,你第一个XML 文件要解析成什么样结果的table
表内容就是上面最后表格的黑色部分,表结构:
CREATE TABLE client_parse_xml(
    client VARCHAR2(20),  
    version VARCHAR2(24),  
    versiontype VARCHAR2(16),  
    account VARCHAR2(50),  
    network VARCHAR2(20),  
    action VARCHAR2(50),  
    count_date VARCHAR2(20),  
    cnt INT,  
    duration INT);

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 2014-7-3 10:02 | 显示全部楼层
写段PL/SQL去处理,比单纯只用SQL去处理要合适

使用道具 举报

回复
论坛徽章:
17
2013年新春福章
日期:2013-02-25 14:51:242015年新春福章
日期:2015-03-04 14:53:16马上加薪
日期:2014-12-18 01:09:30祖国65周年纪念徽章
日期:2014-09-27 08:49:22马上有房
日期:2014-07-16 11:59:32优秀写手
日期:2014-07-10 06:00:12马上加薪
日期:2014-04-20 23:04:51马上有对象
日期:2014-04-19 23:32:33马上有车
日期:2014-03-29 23:05:24问答徽章
日期:2014-01-17 15:19:03
 楼主| 发表于 2014-7-3 11:38 | 显示全部楼层
lastwinner 发表于 2014-7-3 10:02
写段PL/SQL去处理,比单纯只用SQL去处理要合适

PL/SQL我已经搞过一份了,就是想用下oracle xmltype来处理XML,但是好像处理相同元素有些困难

使用道具 举报

回复
认证徽章
论坛徽章:
8
宝马
日期:2013-12-30 15:12:362014年新春福章
日期:2014-02-18 16:44:08马上有对象
日期:2014-02-18 16:44:08青年奥林匹克运动会-击剑
日期:2014-08-26 08:46:14马上加薪
日期:2014-12-15 14:55:28马上加薪
日期:2015-01-30 09:13:41美羊羊
日期:2015-03-19 15:04:19水瓶座
日期:2016-01-28 10:23:13
发表于 2014-7-3 14:34 | 显示全部楼层
是要这种效果吗?  文件lottu就是那个xml内容
  1. awk -F"\"" '/<client/{a=$2;b=$4;c=$6}/user account=/{d=$2}/events count_date/{e=$2}/event network/{print a,b,c,d,e,$2,$4,$6}' lottu | column -t
  2. iphone  2.5.1.0  0000  18888888888  2012-08-19  WiFi    5  20
  3. iphone  2.5.1.0  0000  18888888888  2012-08-19  WiFi    9
  4. iphone  2.5.1.0  0000  18888888888  2012-08-18  WiFi    4
  5. iphone  2.5.1.0  0000  18888888888  2012-08-18  WiFi    5
  6. iphone  2.5.1.0  0000  noaccount    2012-08-19  CMCC3G  2
复制代码

使用道具 举报

回复
论坛徽章:
17
2013年新春福章
日期:2013-02-25 14:51:242015年新春福章
日期:2015-03-04 14:53:16马上加薪
日期:2014-12-18 01:09:30祖国65周年纪念徽章
日期:2014-09-27 08:49:22马上有房
日期:2014-07-16 11:59:32优秀写手
日期:2014-07-10 06:00:12马上加薪
日期:2014-04-20 23:04:51马上有对象
日期:2014-04-19 23:32:33马上有车
日期:2014-03-29 23:05:24问答徽章
日期:2014-01-17 15:19:03
 楼主| 发表于 2014-7-3 15:20 | 显示全部楼层
li0924 发表于 2014-7-3 14:34
是要这种效果吗?  文件lottu就是那个xml内容

nice job awk匹配关键字处理是很强大,尤其是结构比较明确额数据

使用道具 举报

回复
求职 : 数据库管理员
招聘 : Java研发
认证徽章
论坛徽章:
6348
ITPUB9周年纪念徽章
日期:2014-05-02 10:36:402011新春纪念徽章
日期:2014-12-29 12:11:142010广州亚运会纪念徽章:卡巴迪
日期:2014-08-06 08:44:252012新春纪念徽章
日期:2014-12-29 12:11:142013年新春福章
日期:2014-12-29 12:11:14马上有车
日期:2014-12-29 12:11:14马上有房
日期:2014-12-29 12:11:14马上有钱
日期:2014-12-29 12:11:14马上有对象
日期:2014-12-29 12:11:14马上加薪
日期:2014-12-29 12:11:14
发表于 2014-7-3 16:23 | 显示全部楼层
li0924 发表于 2014-7-3 14:34
是要这种效果吗?  文件lottu就是那个xml内容

经常看见老哥用awk 玩文本处理,NB 的很啊

使用道具 举报

回复

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

本版积分规则 发表回复

PostgreSQL中国大会,参会票抢购!

由 PostgreSQL中文社区与ITPUB联合主办的第九届《PostgreSQL 中国技术大会》将在北京隆重召开。PostgreSQL 作为功能最强的的开源关系型数据库之一,得到了越来越多企业的推广和运用,也越来越受到广大技术爱好者的欢迎和重视。这将是 PostgreSQL 的又一次交流盛会。
----------------------------------------
时间:2019年11月29~11月30日

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