If I have a table:
如果我有一张桌子:
create table thisTable (
column1 varchar(20),
column2 varchar(20),
column3 varchar(20)
);
and I want to transfer data to a 1 column XML table:
我想把数据传输到1列XML表:
create table XMLTable (
data1 sys.xmltype
);
<column2>
<column1>..</column1>
<column2>..</column2>
<column3>..</column3>
</column2>
How would I do that?
我该怎么做呢?
2 个解决方案
#1
3
INSERT INTO XMLTABLE
SELECT
XMLELEMENT(
"column2",
XMLELEMENT("column1", COLUMN1), XMLELEMENT("column2", COLUMN2), XMLELEMENT("column3", COLUMN3)
)
FROM
thisTable;
#2
1
You can insert it using below procedure.
您可以使用下面的过程插入它。
declare
sourceTable varchar2(80) := 'THISTABLE';
destTable varchar2(80) := 'XMLTABLE';
destColumn varchar2(80) := 'data1';
TYPE cur_typ IS REF CURSOR;
c cur_typ;
colu varchar2(2000);
vsql varchar2(2000) := ' select ';
begin
for r in (select column_name from user_tab_columns where table_name = sourceTable order by column_id)
loop
vsql := vsql || ''' <' || r.column_name || '>'' || ' || r.column_name || ' || ''</' || r.column_name || '> '' || ' ;
end loop;
vsql := substr(vsql, 0 ,length(vsql)-4);
vsql := vsql ||' as x From ' || sourceTable;
open c for vsql ;
loop
FETCH c INTo colu;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line(colu);
execute immediate ' insert into ' || destTable || ' values (xmltype(''<column2>' || colu || '</column2>'')) ';
end loop;
close c;
end;
#1
3
INSERT INTO XMLTABLE
SELECT
XMLELEMENT(
"column2",
XMLELEMENT("column1", COLUMN1), XMLELEMENT("column2", COLUMN2), XMLELEMENT("column3", COLUMN3)
)
FROM
thisTable;
#2
1
You can insert it using below procedure.
您可以使用下面的过程插入它。
declare
sourceTable varchar2(80) := 'THISTABLE';
destTable varchar2(80) := 'XMLTABLE';
destColumn varchar2(80) := 'data1';
TYPE cur_typ IS REF CURSOR;
c cur_typ;
colu varchar2(2000);
vsql varchar2(2000) := ' select ';
begin
for r in (select column_name from user_tab_columns where table_name = sourceTable order by column_id)
loop
vsql := vsql || ''' <' || r.column_name || '>'' || ' || r.column_name || ' || ''</' || r.column_name || '> '' || ' ;
end loop;
vsql := substr(vsql, 0 ,length(vsql)-4);
vsql := vsql ||' as x From ' || sourceTable;
open c for vsql ;
loop
FETCH c INTo colu;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line(colu);
execute immediate ' insert into ' || destTable || ' values (xmltype(''<column2>' || colu || '</column2>'')) ';
end loop;
close c;
end;