How to do Merge operation from xml to dynamic tables and columns in MSSQL

Question

I have a xml which provides me table name with there respective ID column and column to update or insert on basis of those IDs below is the xml:

<tables>
    <table>
      <name>Table1</name>
       <attr>
         <id>1</id>
         <columns>
           <col_name>col1</col_name>
           <col_val>123</col_val>
         <columns>
          <columns>
           <col_name>col2</col_name>
           <col_val>345</col_val>
         <columns>
       </attr>
        <attr>
         <id>2</id>
         <columns>
           <col_name>col3</col_name>
           <col_val>123</col_val>
         <columns>
       </attr>
        <attr>
         <id>4</id>
         <columns>
           <col_name>col2</col_name>
           <col_val>123</col_val>
         <columns>
       </attr>
    </table>
        <table>
      <name>Table2</name>
       <attr>
         <id>1</id>
         <columns>
           <col_name>coltb1</col_name>
           <col_val>123</col_val>
         <columns>
          <columns>
           <col_name>coltb3</col_name>
           <col_val>345</col_val>
         <columns>
       </attr>
        <attr>
         <id>3</id>
         <columns>
           <col_name>coltb4</col_name>
           <col_val>123</col_val>
         <columns>
       </attr>
    </table>
    
</tables>

In this I have table name which I can match with the table which is created in my db and on basis of ID column, I have to check whether the ID is present or not if present then I have to update the columns which are present in column node with the value and if not present I need to insert to the table.

Below is the code which I have done so far,

;WITH CTE (ID,TableName) As (SELECT ROW_NUMBER() OVER (ORDER BY CAST(y.item.query('data(name)') AS NVARCHAR(300))) AS ROWNUM,
                             CAST(y.item.query('data(name)') AS NVARCHAR(300)) AS TableName
                             FROM @input.nodes('/tables/table') y(item))
                             
SELECT  ID,TableName into #tmp FROM CTE

DECLARE @Counter INT,@tableName nvarchar(300)
SET @Counter=1
WHILE (@Counter<=(SELECT Count(*) FROM #tmp))
BEGIN

    SET @tableName=(SELECT TableName FROM #tmp WHERE ID=@Counter)
    ;WITH CTE2(id) AS (SELECT  CAST(x.item.query('data(id)') AS NVARCHAR(30)) AS id
                             FROM @input.nodes('/tables/table') y(item)
                             CROSS APPLY y.item.nodes('./attr') x(item)
                             WHERE CAST(y.item.query('data(name)') AS NVARCHAR(300))=@tableName)
    SELECT * FROM CTE2
    SET @Counter=@Counter+1
END

Here I am able to fetch the tablename and on looping of it I am getting the ID as well but , I am not able to find the column name and not getting how to do Merge on those column name as it is all dynamic.

Tables which I have in my db are

Table1:

ID|col1|col2|col3|col4|col5
---------------------------
1 |123 |345 |456 |null|89
2 |222 |444 |667 |890 |99

Table2

ID|coltb1|coltb2|coltb3|coltb4|coltb5
------------------------------------
1 |786   |678   |880   |99    |788
2 |345   |678   |667   |9990  |008
3 |344   |667   |623   |945   |678

You will have to use dynamic SQL, however, if this is the requirement it sounds more likely that you have a design flaw. — Thom A, Oct 31 '20 at 10:13
could you please suggest me what else I could do , XML preparation is done by me only its json which I am converting into xml — Daksh Dutta, Oct 31 '20 at 10:15
If you're receiving JSON, why not consume it as JSON? Converting it ti XML seems like a pointless task when SQL Server can read JSON. — Thom A, Oct 31 '20 at 10:16
actually json has junk values in it which I need to manipulate in node and converting it to xml,apart from it I am not that much aware of json parsing sql — Daksh Dutta, Oct 31 '20 at 10:18
Then I suspect you aren't telling us the full story, along with having a design flaw. — Thom A, Oct 31 '20 at 10:19
I have json which are haveing this id column and other columns which needs to update or insert and that json is not in my control, table are created already, i just need to parse the table then basd on on ids I have to update or insert the data in those tables — Daksh Dutta, Oct 31 '20 at 10:21

score 1 · Answer 1 · answered Oct 31 '20 at 13:30

You can use the following as base:

DECLARE @XML XML = N'<tables><table><name>Table1</name><attr><id>1</id><columns><col_name>col1</col_name><col_val>123</col_val></columns><columns><col_name>col2</col_name><col_val>345</col_val></columns></attr><attr><id>2</id><columns><col_name>col3</col_name><col_val>123</col_val></columns></attr><attr><id>4</id><columns><col_name>col2</col_name><col_val>123</col_val></columns></attr></table><table><name>Table2</name><attr><id>1</id><columns><col_name>coltb1</col_name><col_val>123</col_val></columns><columns><col_name>coltb3</col_name><col_val>345</col_val></columns></attr><attr><id>3</id><columns><col_name>coltb4</col_name><col_val>123</col_val></columns></attr></table></tables>';

DROP TABLE IF EXISTS #DataSource;

CREATE TABLE #DataSource
(
    [table_name] SYSNAME
   ,[id] INT
   ,[column_name] SYSNAME
   ,[column_value] INT
);

WITH DataSource ([table_name], [columns_xml]) AS
(
    SELECT T.c.value('./name[1]', 'NVARCHAR(128)')
          ,T.c.query('./attr') 
    FROM @XML.nodes('/tables/table') T(c)
)
INSERT INTO #DataSource ([table_name], [id], [column_name], [column_value])
SELECT [table_name]
      ,T.c.value('(./id)[1]', 'INT')
      ,T.c.value('(./columns/col_name)[1]', 'VARCHAR(12)')
      ,T.c.value('(./columns/col_val)[1]', 'INT')
FROM DataSource
CROSS APPLY [columns_xml].nodes('./attr') T(c);

SELECT *
FROM #DataSource

DECLARE @current_table_name SYSNAME
       ,@current_columns VARCHAR(MAX)
       ,@current_columns_updated VARCHAR(MAX)
       ,@DynamicTSQLStatement NVARCHAR(MAX);

WHILE EXISTS(SELECT 1 FROM #DataSource)
BEGIN;

    SELECT TOP 1 @current_table_name = [table_name]
    FROM #DataSource;

    SELECT @current_columns = STRING_AGG(QUOTENAME([column_name]), ',')   WITHIN GROUP (ORDER BY [column_name] ASC)
          ,@current_columns_updated  = STRING_AGG(QUOTENAME([column_name]) + ' = ISNULL(S.' + QUOTENAME([column_name]) + ', T.' + QUOTENAME([column_name]) + ')', ',') WITHIN GROUP (ORDER BY [column_name] ASC) 
    FROM #DataSource
    WHERE [table_name] = @current_table_name;

    SET @DynamicTSQLStatement = N'
    WITH DataSource AS
    (
        SELECT *
        FROM
        (
            SELECT [id]
                  ,[column_name]
                  ,[column_value]
            FROM #DataSource
            WHERE [table_name] = ''' + @current_table_name + '''
        ) DS
        PIVOT
        (
            MAX([column_value]) FOR [column_name] IN (' + @current_columns + ')
        ) PVT
    )
    MERGE ' + @current_table_name + ' AS T
    USING DataSource AS S
        ON T.[id] = S.[id]
    WHEN MATCHED THEN  
        UPDATE SET  ' + @current_columns_updated + '
    WHEN NOT MATCHED THEN 
        INSERT ([id], ' + @current_columns + ')
        VALUES (S.[id], ' + @current_columns + ');';
    
    EXEC sp_executesql @DynamicTSQLStatement;

    DELETE FROM #DataSource
    WHERE [table_name] = @current_table_name;

END;

How to do Merge operation from xml to dynamic tables and columns in MSSQL

1 Answers1