Simpan XML ke MSSQL

Saya mencoba menyimpan XML ini ke dalam database MSSQL (XML ada di bawah).

Saya telah menggunakan kode ini sebagai titik awal: https://www.mssqltips.com/sqlservertip/5707/simple-way-to-import-xml-data-into-sql-server-with-tsql/

Semuanya berfungsi dengan baik (ya!), kecuali bidang terakhir Transaction_ID (menyedihkan...). 4 nilai bidang terakhir ditempatkan satu sama lain dalam tabel SQL. Dan saya ingin memiliki nilai-nilai dalam catatan terpisah.

Ini adalah bagian dari kode yang saya gunakan untuk mengubah bidang terakhir menjadi MSSQL:

MY_XML.XML1.query('Transactions/Transaction_ID').value('.','VARCHAR(255)')



<?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>
  1. apakah model data kita yang bermasalah?
  2. apakah XML yang kami terima bermasalah?
  3. apakah kode saya yang bermasalah?
  4. atau apakah saya perlu menyimpan ID_transaksi dan kunci dalam tabel SQL terpisah?

Terima kasih!

Semoga sukses, Sharon


xml tsql sql-server-2019 xquery
person Sharon    schedule 25.06.2020    source sumber
comment
Kode Anda adalah masalahnya. Cuplikan kode tampaknya berasal dari daftar kolom SELECT sehingga mengembalikan semua teks dari node yang cocok. Anda perlu menentukannya dalam klausa FROM (mungkin menggunakan metode nodes) untuk mengembalikan beberapa elemen Transaction_ID sebelum mengekstraksi teks elemen menggunakan metode value. Tambahkan kueri lengkap ke pertanyaan Anda jika Anda memerlukan bantuan untuk itu.   -  person Dan Guzman    schedule 25.06.2020
comment
Sharon, Saat mengajukan pertanyaan, Anda perlu memberikan contoh minimal yang dapat direproduksi. Silakan merujuk ke tautan berikut: stackoverflow.com/help/minimal-reproducible-example Harap berikan yang berikut: ( 1) DDL untuk tabel target Anda, yaitu T-SQL CREATE tabel. (2) Apa yang perlu Anda lakukan, yaitu logika dan kode T-SQL Anda mencoba menerapkannya. (3) Versi SQL Server Anda (PILIH @@versi;)   -  person Yitzhak Khabinsky    schedule 25.06.2020

Jawaban (2)


arrow_upward
0
arrow_downward

Sesuatu seperti ini?

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)

keluaran mana

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
person David Browne - Microsoft    schedule 25.06.2020

arrow_upward
0
arrow_downward

Anda dapat mencoba yang berikut ini untuk mengonversi file SETIAP XML menjadi Tabel SQL. Ini menggunakan format xml untuk menentukan apakah itu rekaman baru atau bukan. Itu juga memisahkan atribut sebagai rekor baru tetapi menandainya. Anda dapat dengan mudah merekonstruksi kembali xml

MENGGUNAKAN:

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

PROSEDUR:

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
person Ray    schedule 17.08.2020