משתמשים ב-XMLType של אורקל? כדאי שתקראו את זה

XMLType הוא טיפוס נתונים ותיק עוד מימי אורקל 8i. לרוב אנחנו צריכים לאנדקס ערכים מתוך ה-XML שיכולים להופיע ב-elements או ב-attributes שבתוך ה-XML. שימוש שכיח לכך הוא ע"י הרכבת אינדקס על פונקציה המבצעת קריאה ל-ExtractValue באופן הבא:

CREATE INDEX emp_id_ix ON employees

  (extractValue(XMLCol, '/Employees/Id'));

 

אבל מה יקרה אם ה-XML מכיל מס' אלמנטים בעלי אותו שם באותה רמה בעץ? לדוגמה:

<Employees>

            <Id> 123</Id>

            <Id> 234</Id>

</Employees>

אם ננסה ליצור את האינדקס על  ה-XML שבדוגמה נקבל שגיאה:

ORA-19025: EXTRACTVALUE returns value of only one node

הסיבה לשגיאה היא ש-ExtractValue לא יכול להחזיר יותר מערך אחד. אז מה עושים?

האמת – אני לא ממש מוצא פתרון אינטואיטיבי לזה. אשמח לקבל תגובות מאנשים לגבי פתרונות פשוטים.

הפתרון שלי הוא – להוציא החוצה את הערך לאינדוקס לתוך טבלה. כדי להקל על תהליך ההזנה של הנתונים, אפשר ליצור טריגר שמוציא את הערך שיש לאנדקס מתוך ה-XML החוצה לטבלה באופן אוטומטי.

דוגמה:

נניח שיש לי טבלה עם עמודה XML שנקראת xmltab:

create table xmltab (xmlid int, xmlcol xmltype );

ניצור "טבלת בת" שתכיל את הערכים שנוציא החוצה מה-XML:

create table xmltab_Vals (id int, xmlid int, val int);

create sequence xmltab_vals_id start with 1;

כעת, ניצור טריגר שיזין את טבלת הבת באופן אוטומטי ע"י הוצאת הערכים מה-XML

CREATE OR REPLACE
TRIGGER TR_XMLTAB
AFTER INSERT ON XMLTAB
REFERENCING OLD AS oldval NEW AS newval
FOR EACH ROW 
BEGIN
  insert into xmltab_Vals
    SELECT xmltab_vals_id.nextval, :newval.id, extractvalue(value(d),'/id')
      FROM table(xmlsequence(
                 extract(:newval.xmlcol ,'/employees/id'))) d;

END;

ה-select שבטריגר הופך את הערכים של האלמנט Id לטבלה שמכילה קטעי XML עבור כל Id בנפרד. בשלב הבא, נעשה ExtractValue כדי לחלץ את ה-Id. הפעם לא נקבל את השגיאה ORA-19025 והערכים יסתדרו להם יפה בתוך הטבלה xmltab_Vals.

אם נזין את ה-XML שהבאתי כדוגמה, נקבל שתי רשומות בטבלת xmltab_vals עם הערכים 123 ו-234.

הבעיה של הפתרון הזה היא שלמעשה יש לנו כפילות נתונים ודה-נרמול. אבל יש לציין שברובם המכריע של המקרים, ה-XML הוא נתון סטטי שלא משתנה ולכן לא יהיה צורך בעדכון כפול של ה-XML ושל טבלת הבת.

כמובן שכדאי ליצור fk עם on delete cascade בין הטבלאות הנ"ל ויש לכוון את השאילתות מול טבלת הבת במקום להשתמש ב-extractValue.

אשמח לשמוע רעיונות נוספים.