SQL: להפוך XML במבנה לא ידוע (חלקית) לתצוגה טבלאית

14 בNovember 2015

תגיות: , ,
אין תגובות

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

למשל, אם אנחנו רוצים לשלוף מ- sys.tables ולהציג את התוצאות כ-XML בפורמט טבלאי, כל שצריך לעשות זה להוסיף FOR XML statement בסוף. ניתן בצורה הזאת גם להשיג שליטה מוגבלת על מבנה ה-XML שמתקבל. למשל, בדוגמא הבאה, אני מגדיר שיהיה root element בשם items ושכל שורה תהיה עטופה בתור item:

SELECT *

FROM sys.tables

FOR XML PATH('item'), ROOT('items')

ככה ייראה ה-XML שיתקבל:

image

אבל, מה קורה כשאנחנו רוצים להפוך את ה-XML הזה לתצורה טבלאית בחזרה? לצורך כך יש לנו את הפונקציה OPENXML. הפונקציה הזאת יודעת לקבל handle שמתקבל ע”י קריאה ל- sp_xml_preparedocument שעושים לפני כן, ויודעת להפוך את ה-XML הזה לתצורה של שורות וטבלאות.

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

מה אפשר לעשות? לחלק את הבעייה לשתי שלבים: בשלב הראשון, לקבל את השמות של האלמנטים. בשלב השני, לקרוא ל- OPENXML ולהעביר את שמות האלמנטים שיקבלנו.

ה-stored procedure הבא עושה בדיוק את זה.. מייצר את רשימת העמודות מה-XML (הוא מצפה ל-XML במבנה של root element בשם items ובתוכו אלמנטים בשם item) ואז משתמש ב-OPENXML בשביל להפוך את ה-XML ל-rowset עם שמות העמודות שמועברות דינמית:

 

CREATE PROCEDURE [dbo].[ConvertXmlToRowset]

(

    @input xml

)

AS

BEGIN

    DECLARE @cols table (ColName nvarchar(500))

 

    INSERT INTO @cols(ColName)

    SELECT    

            keyValueSet.Name

    FROM   @input.nodes('/items/item') T(c)

    OUTER APPLY (

                    SELECT CAST(T.c.query('.') as xml) xmlData

                )  rowXml

    OUTER APPLY (

                    SELECT

                        C.Name

                    FROM rowXml.xmlData.nodes('/item/*') as T(C)

                    OUTER APPLY (

                                    SELECT

                                        T.C.value('local-name(.)', 'nvarchar(max)') as Name

                                    UNION ALL

                                    SELECT

                                        A.C.value('.', 'nvarchar(max)') as Value

                                    FROM T.C.nodes('@*') as A(C)

                                ) as C

                ) keyValueSet

 

    DECLARE @colsNames NVARCHAR(2000)

    SELECT @colsNames =COALESCE(@colsNames + ', ', '') + '[' +ColName + '] nvarchar(max)'

    FROM @cols

    GROUP BY ColName

 

    DECLARE @query nvarchar(max)=

    '

    DECLARE @doc nvarchar(max) = ''' + CAST(@input as nvarchar(max)) + '''

    DECLARE @idoc int 

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    

    SELECT    *

    FROM       OPENXML (@idoc, ''/items/item'',2)

            WITH (' + @colsNames + ')

    '

    EXECUTE(@query)

END

 

 

 

GO

 

 

ודוגמא לשימוש:

DECLARE @xml xml = (SELECT * FROM sys.tables FOR XML PATH('item'), ROOT('items'))

EXECUTE redisql.ConvertXmlToRowset @input = @xml

image

החסרון בשיטה הזאת, הוא שה-rowset שמתקבל בסוף לא זהה למקורי. ה- data type המקורי של העמודות לא נשמר, ובמימוש הזה גם הסדר לא נשמר. הפיתרון הנ”ל היה אחד הפתרונות ששקלתי כשמימשתי את שמירת ה- Rowsets ב- RediSql, ופסלתי אותו בסוף בגלל הסיבה הזאת – הוא לא מייצג נאמנה את המקור. עם זאת, במידה שזה פחות חשוב לכם ומבחינתכם לקבל את כל המידע בתור nvarchar(max) מתאים, עדיין יכול להיות מדובר בפיתרון שימושי.

בהצלחה.

הוסף תגובה
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *