Сохранить XML в MSSQL

Я пытаюсь сохранить этот XML в базу данных MSSQL (XML ниже).

Я использовал этот код в качестве отправной точки: https://www.mssqltips.com/sqlservertip/5707/simple-way-to-import-xml-data-into-sql-server-with-tsql/

Все работает хорошо (ага!), кроме последнего поля Transaction_ID (облом...). 4 значения последнего поля располагаются друг за другом в таблице SQL. И я хотел бы иметь значения в отдельных записях.

Это часть кода, который я использовал для преобразования последнего поля в 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. проблема в нашей модели данных?
  2. Является ли XML, который мы получили, проблемой?
  3. мой код проблема?
  4. или мне нужно сохранить идентификаторы транзакций и ключ в отдельной таблице SQL?

Спасибо!

Всего наилучшего, Шарон


xml tsql sql-server-2019 xquery
person Sharon    schedule 25.06.2020    source источник
comment
Ваш код является проблемой. Фрагмент кода выглядит как из списка столбцов SELECT, поэтому он возвращает весь текст соответствующих узлов. Вам нужно указать его в предложении FROM (возможно, используя метод nodes), чтобы вернуть несколько элементов Transaction_ID перед извлечением текста элемента с помощью метода value. Добавьте полный запрос к своему вопросу, если вам нужна помощь в этом.   -  person Dan Guzman    schedule 25.06.2020
comment
Шарон, Задавая вопрос, нужно привести минимальный воспроизводимый пример. Перейдите по следующей ссылке: stackoverflow.com/help/minimal-reproducible-example Укажите следующее: ( 1) DDL для вашей целевой таблицы (таблиц), т. е. таблицы (таблиц) T-SQL CREATE. (2) Что вам нужно сделать, т. е. логика и ваш код T-SQL пытаются ее реализовать. (3) Ваша версия SQL Server (SELECT @@version;)   -  person Yitzhak Khabinsky    schedule 25.06.2020

Ответы (2)


arrow_upward
0
arrow_downward

Что-то вроде этого?

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

arrow_upward
0
arrow_downward

Вы можете попробовать следующее, чтобы преобразовать ЛЮБОЙ файл 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
person Ray    schedule 17.08.2020