資料庫表格轉換成XML格式方法！

資料庫表格轉換成XML格式方法！ 資源中心有軟體可下載
1，確定必要的檢視或者是表格
2，建立儲存過程
create or replace procedure SP_create_xml(obj_name varchar2) is
ls_object_name user_tab_columns.TABLE_NAME%type;
ln_object_hava number:=0;
type ty_obj_cols is table of user_tab_columns.COLUMN_NAME%type
index by binary_integer;
ty_obj_col ty_obj_cols;
ln_col_no number:=1;
ls_creaet_sql varchar2(5000);

begin
dbms_output.enable(200000);
--------------------獲得表的名稱並驗證--------------------------
----------------------------------------------------------------
ls_object_name:=upper(obj_name);
select count(*) into ln_object_hava from user_tab_columns where table_name=ls_object_name;
if ln_object_hava=0 then
dbms_output.put_line('& the object: '||obj_name||' can not be found!!');
return;
--調出程式！！
end if;
----------------------------------------------------------------
-------------------獲得列的資訊 -------------------------------
for cur_01 in (select COLUMN_NAME from user_tab_columns where table_name= ls_object_name)
loop
ty_obj_col(ln_col_no):=cur_01.COLUMN_NAME;
ln_col_no:=ln_col_no+1;
end loop;
----------------------------------------------------------------
---------------------列印所有的列資訊---------------------------
--for i in 1..ty_obj_col.count loop
-- dbms_output.put_line('## '||ty_obj_col(i));
--end loop;
----------------------------------------------------------------
--------------------獲得主鍵資訊 ----------------------------

----------------------------------------------------------------
----------------------------------------------------------------
ls_creaet_sql:='create or replace view xml_'||lower(obj_name)||CHR(10)||' as '||CHR(10)||'select '||CHR(10);
ls_creaet_sql:=ls_creaet_sql||' ''''||CHR(10)||'||CHR(10);
for i in 1..ty_obj_col.count loop
ls_creaet_sql:=ls_creaet_sql||' '||' '' ''|| ''''||'||ty_obj_col(i)||'||'''||''||ty_obj_col(i)||'>''||' ||'CHR(10)||'||CHR(10);
end loop;
ls_creaet_sql:=ls_creaet_sql||''''''||CHR(10);
ls_creaet_sql:=ls_creaet_sql||' XML_INFO FROM '||obj_name||'';
-----------------------------------------------------------------
-----------------------使用換行的辦法列印出來--------------------
dbms_output.put_line('使用換行的辦法列印出來');
dbms_output.put_line('------------------------------------------------------');
declare
ln_pos number:=1;
ln_poss number:=1;
ln_posss number:=1;
begin
while instr(ls_creaet_sql,chr(10),1,ln_pos)>0 loop
ln_posss:=instr(ls_creaet_sql,chr(10),1,ln_pos);
--dbms_output.put_line(ln_posss||' --- '||ln_poss);
dbms_output.put_line(substrb(ls_creaet_sql,ln_poss,ln_posss-ln_poss));
ln_pos:=ln_pos+1;--需要依次遞加！！
ln_poss:=ln_posss;
end loop;
dbms_output.put_line(substrb(ls_creaet_sql,ln_poss,ln_posss));
end;
-----------------------------------------------------------------
-----------------------------------------------------------------
-----------------------------------------------------------------
execute immediate rtrim(ls_creaet_sql,chr(10));
dbms_output.put_line('------------------------------------------------------');
exception
when others then
dbms_output.put_line(substrb(sqlerrm,1,200));
end SP_create_xml;
/
3，執行可以建立XML檢視
4，利用檢視生成XML檔案!!利用資源中心的FORMAT_XML程式可以格式化成標準的XML檔案!
詳細見軟體！！

[@more@]