How dynamically insert data in respective tables based upon xml in Sql server

Question

I have this type of input XML parameter pass in stored procedure, which is passed from my .net application.

Now, I need to insert records in each respective table:

<root>
<table1>
<patid>123</patid>
<name>gresh</name>
<fname>kumar</name>
</table1>
<table2>
<patid>123</patid>
<Schoolname>12345</Schoolname>
</table2>
<tablen>
<patid>123</patid>
<nfield>12345</nfield>
</tablen>
<root>

Suppose table1 will insert data in table1, table2 data in table2, tablen mean number of other tables may be there in XML.

So how it would be possible to insert in each respective tables?

Answer 1

You first need to get the data from the XML. Your case is pretty simple (and I am assuming you are inserting data only in one table only once, but you can solve it for multiple insertions in one table easily). So, the following statement gives me:

SELECT T.c.value('local-name(.)','nvarchar(128)') AS tableName
      ,T1.c1.value('local-name(.)','nvarchar(128)') AS columName
      ,T1.c1.value('(./node())[1]','nvarchar(max)') AS value
FROM @XML.nodes('/root/*') T(c)
CROSS APPLY T.c.nodes('*') T1(c1)

Then, you need to group the values for insertion for each table (I am using a CLR concatenate function (you can find it here, but you can search for T-SQL alternative also):

;WITH DataSource(tableName, columName, value)AS
(
    SELECT T.c.value('local-name(.)','nvarchar(128)')
          ,T1.c1.value('local-name(.)','nvarchar(128)')
          ,T1.c1.value('(./node())[1]','nvarchar(max)')
    FROM @XML.nodes('/root/*') T(c)
    CROSS APPLY T.c.nodes('*') T1(c1)
)
SELECT tableName
      ,[dbo].[Concatenate] (columName)
      ,[dbo].[Concatenate] (value)
FROM DataSource
GROUP BY tableName

You can prepare the above code for dynamic execution further like this:.

;WITH DataSource(tableName, columName, value)AS
(
    SELECT T.c.value('local-name(.)','nvarchar(128)')
          ,T1.c1.value('local-name(.)','nvarchar(128)')
          ,T1.c1.value('(./node())[1]','nvarchar(max)')
    FROM @XML.nodes('/root/*') T(c)
    CROSS APPLY T.c.nodes('*') T1(c1)
)
SELECT 'INSERT INTO ' + tableName  + '(' + [dbo].[Concatenate] (columName) + ') VALUES(' + [dbo].[Concatenate] ('''' + value + '''') + ');' 
FROM DataSource
GROUP BY tableName

Now, you simple need to build a dynamic T-SQL string and execute it using sp_executesql procedure:

DECLARE @XML XML = N'<root>
<table1>
<patid>123</patid>
<name>gresh</name>
<fname>kumar</fname>
</table1>
<table2>
<patid>123</patid>
<Schoolname>12345</Schoolname>
</table2>
<tablen>
<patid>123</patid>
<nfield>12345</nfield>
</tablen>
</root>';

DECLARE @DynamicSQLStatement NVARCHAR(MAX)

;WITH DataSource(tableName, columName, value)AS
(
    SELECT T.c.value('local-name(.)','nvarchar(128)')
          ,T1.c1.value('local-name(.)','nvarchar(128)')
          ,T1.c1.value('(./node())[1]','nvarchar(max)')
    FROM @XML.nodes('/root/*') T(c)
    CROSS APPLY T.c.nodes('*') T1(c1)
)
SELECT @DynamicSQLStatement =
(
    SELECT CHAR(10) + 'INSERT INTO ' + tableName  + '(' + [dbo].[Concatenate] (columName) + ') VALUES(' + [dbo].[Concatenate] ('''' + value + '''') + ');' 
    FROM DataSource
    GROUP BY tableName
    FOR XML PATH, TYPE
).value('.', 'NVARCHAR(MAX)')

PRINT @DynamicSQLStatement

EXEC sp_executesql @DynamicSQLStatement

Note, that I am assuming that your XML structure is valid in the context of the database - the table exists, the column exists, and the values inserted in the columns are correct.

You can add more check if you need.