บันทึก XML ลงใน MSSQL

อะไรแบบนี้เหรอ?

declare @doc xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root>
<Session>
<Record_ID>000029837OYMV22fdewSY12IuRBUlxqq=</Record_ID>
<Extraction_Date>2020-03-25T14:02:29</Extraction_Date>
<Company_ID>Company1</Company_ID>
<ID>Product2</ID>
<Session_ID>000029837OYMV22fdewSY12IuRBUlxqq=</Session_ID>
<Session_Start_Datetime>2019-11-21T15:21:59</Session_Start_Datetime>
<Session_End_Datetime>2020-05-11T15:26:36</Session_End_Datetime>
<Session_Commission>25</Session_Commission>
<Transactions>
<Transaction_ID>1</Transaction_ID>
<Transaction_ID>2</Transaction_ID>
<Transaction_ID>3</Transaction_ID>
<Transaction_ID>4</Transaction_ID>
</Transactions>
</Session>
</Root>'

select 
    session.value('(Record_ID)[1]', 'varchar(40)') Record_ID,
    session.value('(Session_End_Datetime)[1]', 'datetime') Session_End_Datetime,
    tranid.value('.', 'int') Transaction_ID
from @doc.nodes('/Root/Session') doc(session)
cross apply session.nodes('Transactions/Transaction_ID') transactions(tranid)

ซึ่งส่งออก

Record_ID                                Session_End_Datetime    Transaction_ID
---------------------------------------- ----------------------- --------------
000029837OYMV22fdewSY12IuRBUlxqq=        2020-05-11 15:26:36.000 1
000029837OYMV22fdewSY12IuRBUlxqq=        2020-05-11 15:26:36.000 2
000029837OYMV22fdewSY12IuRBUlxqq=        2020-05-11 15:26:36.000 3
000029837OYMV22fdewSY12IuRBUlxqq=        2020-05-11 15:26:36.000 4

คุณสามารถลองทำสิ่งต่อไปนี้เพื่อแปลงไฟล์ XML ใดก็ได้ เป็นตาราง SQL ใช้รูปแบบ xml เพื่อตรวจสอบว่าเป็นระเบียนใหม่หรือไม่ นอกจากนี้ยังแยกแอททริบิวต์เป็นเรกคอร์ดใหม่แต่ตั้งค่าสถานะไว้ คุณสามารถสร้าง xml กลับขึ้นมาใหม่ได้อย่างง่ายดาย

ใช้:

select *  from Utility.FlattenXml('your xml’)

ขั้นตอน:

USE [YOUR_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [Utility].[FlattenXml](@xmlDoc XML)  
RETURNS TABLE 
AS RETURN 
WITH CTE AS ( 

    SELECT 
            1 AS lvl,  
            x.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
            CAST(NULL AS NVARCHAR(MAX)) AS ParentName, 
            CAST(1 AS INT) AS ParentPosition, 
            CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
            x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,  
            x.value('local-name(.)','NVARCHAR(MAX)')  
            + N'[' 
            + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)  
            + N']' AS XPath,  
            ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position, 
            x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,  
            x.value('text()[1]','NVARCHAR(MAX)') AS Value,  
            x.query('.') AS this,         
            x.query('*') AS t,  
            CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,  
            CAST(1 AS INT) AS ID  
    FROM @xmlDoc.nodes('/*') a(x)  
    UNION ALL 
    SELECT 
            p.lvl + 1 AS lvl,  
            c.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
            CAST(p.Name AS NVARCHAR(MAX)) AS ParentName, 
            CAST(p.Position AS INT) AS ParentPosition, 
            CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
            CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath,  
            CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')+ N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') 
            ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,  
            ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
            ORDER BY (SELECT 1)) AS Position, 
            CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,  
            CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, c.query('.') AS this,  
            c.query('*') AS t,  
            CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,  
            CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)
            
    FROM CTE p  
    CROSS APPLY p.t.nodes('*') b(c)), cte2 AS (  
                                                SELECT 
                                                lvl AS Depth,  
                                                Name AS NodeName,  
                                                ParentName, 
                                                ParentPosition, 
                                                NodeType,  
                                                FullPath,  
                                                XPath,  
                                                Position, 
                                                Tree AS TreeView,  
                                                Value,  
                                                this AS XMLData,  
                                                Sort, 
                                                ID
                                                FROM cte  
    UNION ALL 
    SELECT 
            p.lvl,  
            x.value('local-name(.)','NVARCHAR(MAX)'),  
            p.Name, 
            p.Position, 
            CAST(N'Attribute' AS NVARCHAR(20)),  
            p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
            p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
            1, 
            SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)  
            + N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
            x.value('.','NVARCHAR(MAX)'),  
            NULL,  
            p.Sort,  
            p.ID + 1  
    FROM CTE p  
    CROSS APPLY this.nodes('/*/@*') a(x)  
    )  

    SELECT into [your table]
            ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,  
            ParentName, ParentPosition,Depth, NodeName, Position,   
            NodeType, FullPath, XPath, TreeView, Value, XMLData 
    FROM CTE2