今天跟同事商討整合規格時,遇到一個議題: 如果將多筆資料轉成XML Element,以XML String方式寫入ORACLE欄位,在Stored Procedure裡可否比照Table,用SELECT的方式將多筆資料查詢出來呢?

剛好前幾天另一位同事提到Oracle 9+支援XmlType,現學現賣,再參考網路上的資料,我寫了以下的範例:

DECLARE
  xt XmlType;
  TYPE refCur IS REF CURSOR;
  rc refCur;
  t VARCHAR2(1024);
  i NUMBER;
BEGIN
    xt := XmlType(
'<root><item>Item1</item><item>Item2</item><item>Item3</item></root>');
    
    OPEN rc FOR
      SELECT extractValue(value(x), '//item/text()') as ItemText
      FROM TABLE(XmlSequence(EXTRACT(xt, '//item'))) X;
 
    i := 0;
    
    LOOP
      FETCH rc INTO t;
      EXIT WHEN rc%NOTFOUND; 
      i := i + 1;
      dbms_output.put_line(i || '->' || t);
    
    END LOOP;
END;

執行結果為:

1->Item1
2->Item2
3->Item3

測試成功!


Comments

# by James

請問您是用什麼方法將XML文件的內容導入xt變數中呢?

# by James

請問您是用什麼方式將XML文件內容導入XT參數呢?

# by Jeffrey

to James, XmlType('....')的寫法可以將(N)VarChar2型別轉成XmlType,因此在Procedure中可以用Parameter接入VarChar2或是直接由Table中SELECT VarChar2欄位,當成XmlType()的參數,就可以得到Xml資料型別了。

# by James

您的意思是假設已經有一個Table的欄位型態為VarChar2,欄位裡的資料已經是XML類似的文字。 例如: <root><item>Item1</item><item>Item2</item><item>Item3</item></root> 而我原先的想法是,以上這段文字還存在*.XML的文件中,我以為有什麼方法可以將*.XML文件"直接"拿來灌入Procedure之中。 如果無法直接將XML文件當Procedure的參數的話,您又是用什麼方式將XML文件內容灌入資料表的VarChar2欄位呢?

# by Jeffrey

to James, 我們的應用情境中,XML由.NET程式產生後直接寫入資料庫VARCHAR欄位,後續其他.NET程式則是讀出修改後再寫回去,過程中沒有轉存到檔案。會引發這個議題是有個Procedure想將XML中的多筆資料拿來JOIN使用,原本的解決方案是另建一個Table,在.NET中讀出XML的Node再變成一筆筆資料INSERT進另建的Table。由於不想為了這個小需求多生一個Table出來,才想到直接把XML字串轉成Table這一招。如果要將XML資料灌入Varchar2,我應該會用File.ReadAllText讀出字串,再用OracleCommand, OracleParameter的方式把它當成一般字串寫入。希望有解答你的疑惑。

# by James

了解,我本以為只用Oracle有方法可以讀XML檔案,原來不是這樣,還要寫一些程式輔助,謝謝了。

Post a comment


68 - 14 =