6

今天跟同事商討整合規格時，遇到一個議題: 如果將多筆資料轉成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