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