高分求教,Oracle 解析Pivot 生成的XML转为table,满意之后再加分
需求:item个数不确定,xml文件如下,有多少个item就对应多少列,<PivotSet><item><columnname="LEVELNO">B2F</column...
需求:item个数不确定,xml文件如下,有多少个item就对应多少列,
<PivotSet>
<item>
<column name = "LEVELNO">B2F</column>
<column name = "SUM(LEVELQUALIFIEDRATIO)">21.7</column></item>
<item><column name = "LEVELNO">B2R</column><column name = "SUM(LEVELQUALIFIEDRATIO)"></column></item><item><column name = "LEVELNO">C2F</column><column name = "SUM(LEVELQUALIFIEDRATIO)">11.1</column></item><item><column name = "LEVELNO">C3F</column><column name = "SUM(LEVELQUALIFIEDRATIO)">12.7</column></item><item><column name = "LEVELNO">C4F</column><column name = "SUM(LEVELQUALIFIEDRATIO)">6.7</column></item><item><column name = "LEVELNO">X2F</column><column name = "SUM(LEVELQUALIFIEDRATIO)"></column></item><item><column name = "LEVELNO">X3F</column><column name = "SUM(LEVELQUALIFIEDRATIO)"></column></item><item><column name = "LEVELNO">X3L</column><column name = "SUM(LEVELQUALIFIEDRATIO)"></column></item></PivotSet> 展开
<PivotSet>
<item>
<column name = "LEVELNO">B2F</column>
<column name = "SUM(LEVELQUALIFIEDRATIO)">21.7</column></item>
<item><column name = "LEVELNO">B2R</column><column name = "SUM(LEVELQUALIFIEDRATIO)"></column></item><item><column name = "LEVELNO">C2F</column><column name = "SUM(LEVELQUALIFIEDRATIO)">11.1</column></item><item><column name = "LEVELNO">C3F</column><column name = "SUM(LEVELQUALIFIEDRATIO)">12.7</column></item><item><column name = "LEVELNO">C4F</column><column name = "SUM(LEVELQUALIFIEDRATIO)">6.7</column></item><item><column name = "LEVELNO">X2F</column><column name = "SUM(LEVELQUALIFIEDRATIO)"></column></item><item><column name = "LEVELNO">X3F</column><column name = "SUM(LEVELQUALIFIEDRATIO)"></column></item><item><column name = "LEVELNO">X3L</column><column name = "SUM(LEVELQUALIFIEDRATIO)"></column></item></PivotSet> 展开
1个回答
推荐于2016-06-27
展开全部
create or replace procedure ljz_pivot_xml_sp(pi_table_name varchar2,
pi_column_name varchar2,
pi_create_table varchar2) as
v_column nvarchar2(50);
v_count number := 0;
v_i number;
v_parent_node nvarchar2(4000);
v_child_node nvarchar2(4000);
v_over boolean := false;
v_tmp nvarchar2(50);
v_existsnode number;
v_sql clob;
v_name varchar2(30);
v_name_xml xmltype;
begin
v_sql := 'select x.* from ' || pi_table_name ||
' a, xmltable(''/PivotSet'' passing a.' ||
pi_column_name || ' columns ';
v_parent_node := '/PivotSet';
v_child_node := 'item[1]/column[2]';
v_i := 1;
execute immediate 'select ' || pi_column_name || ' from ' ||
pi_table_name || ' where rownum=1'
into v_name_xml;
select existsnode(v_name_xml,
'/PivotSet/item[' || to_char(v_i) || ']/column[1]')
into v_existsnode
from dual;
while v_existsnode = 1 loop
execute immediate 'select substr(extractvalue(' || pi_column_name ||
', ''/PivotSet/item[' || to_char(v_i) || ']/column[1]''),1,30)
from ' || pi_table_name || ' x'
into v_name;
v_sql := v_sql || '"' || v_name || '" varchar2(30) path ''item[' ||
to_char(v_i) || ']/column[2]'',';
v_i := v_i + 1;
select existsnode(v_name_xml,
'/PivotSet/item[' || to_char(v_i) || ']/column[1]')
into v_existsnode
from dual;
end loop;
v_sql := trim(',' from v_sql) || ') x';
commit;
select count(1)
into v_count
from 宏姿user_tab_columns
where table_name = upper(pi_create_table);
if v_count = 0 then
execute immediate '含空create table ' || pi_create_table || ' as ' || v_sql;
end if;
end;
调蔽老绝用方法:
begin
ljz_pivot_xml_sp('(select * from (select deptno,sal from emp) pivot xml(sum(sal) for deptno in(any)))',
'deptno_xml',
'ljz_pivot_tmp');
end;
详细见:http://www.cnblogs.com/mellowsmile/p/4642306.html
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询