如何在MSSQL中实现xml到动态表和列的合并操作

DECLARE @xmlData XML = '
<root>
  <table name="Person">
    <column name="ID" type="int"/>
    <column name="Name" type="varchar(50)"/>
  </table>
  <table name="Address">
    <column name="ID" type="int"/>
    <column name="Street" type="varchar(100)"/>
    <column name="City" type="varchar(50)"/>
  </table>
</root>'

CREATE TABLE #dynamicTable (
  TableName VARCHAR(50),
  ColumnName VARCHAR(50),
  ColumnType VARCHAR(50)
)

INSERT INTO #dynamicTable (TableName, ColumnName, ColumnType)
SELECT 
  x.value('@name', 'VARCHAR(50)'),
  c.value('@name', 'VARCHAR(50)'),
  c.value('@type', 'VARCHAR(50)')
FROM @xmlData.nodes('/root/table') AS t(x)
CROSS APPLY t.x.nodes('column') AS c(c)

DECLARE @sql VARCHAR(MAX) = ''

SELECT @sql = @sql + 'CREATE TABLE ' + TableName + ' (' +
                STUFF((SELECT ', ' + ColumnName + ' ' + ColumnType
                       FROM #dynamicTable
                       WHERE TableName = t.TableName
                       FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') +
                '); '
FROM (SELECT DISTINCT TableName FROM #dynamicTable) AS t

EXECUTE (@sql)

DECLARE @insertTemplate VARCHAR(MAX) = 'INSERT INTO TableName (ColumnNames) VALUES (ColumnValues); '

SET @sql = ''

SELECT @sql = @sql + REPLACE(REPLACE(@insertTemplate, 'TableName', TableName), 'ColumnNames', 
                              STUFF((SELECT ', ' + ColumnName
                                     FROM #dynamicTable
                                     WHERE TableName = t.TableName
                                     FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')) +
                    REPLACE(REPLACE(REPLACE(ColumnValues, 'TableName', TableName), 'ColumnNames', 
                                      STUFF((SELECT ', ' + ColumnName
                                             FROM #dynamicTable
                                             WHERE TableName = t.TableName
                                             FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')), 'ColumnValues', 
                                      STUFF((SELECT ', ' + ColumnName
                                             FROM #dynamicTable
                                             WHERE TableName = t.TableName
                                             FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, ''))
FROM (SELECT DISTINCT TableName,
              STUFF((SELECT ', ''' + ColumnName + ''''
                     FROM #dynamicTable
                     WHERE TableName = t.TableName
                     FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS ColumnNames,
              STUFF((SELECT ', ' + ColumnName
                     FROM #dynamicTable
                     WHERE TableName = t.TableName
                     FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS ColumnValues
      FROM #dynamicTable AS t) AS t

EXECUTE (@sql)