基于XML数据动态创建临时表（无需硬编码列名）

针对你提供的XML数据，下面是SQL Server环境下的实现方案，全程动态处理可变的列名和列数：

实现步骤 #

1. 从XML的第一行提取所有元素名，作为临时表的列名
2. 动态生成创建临时表的SQL语句并执行
3. 解析XML中的所有行数据，批量插入到临时表中

完整代码（兼容SQL Server 2017+） #

DECLARE @XmlData XML = N'
<LocationsData>
  <Row>
    <AccountId>1</AccountId>
    <ActivityId>3</ActivityId>
    <CountryCode>USA</CountryCode>
  </Row>
  <Row>
    <AccountId>2</AccountId>
    <ActivityId>5</ActivityId>
    <CountryCode>AU</CountryCode>
  </Row>
</LocationsData>';

-- 提取第一行的所有列名
DECLARE @Columns NVARCHAR(MAX);
SELECT @Columns = STRING_AGG(QUOTENAME(c.value('local-name(.)', 'NVARCHAR(128)')), ', ')
FROM @XmlData.nodes('/LocationsData/Row[1]/*') AS t(c);

-- 动态创建临时表
DECLARE @CreateTableSQL NVARCHAR(MAX) = N'CREATE TABLE #TempData (' + @Columns + ' NVARCHAR(MAX));';
EXEC sp_executesql @CreateTableSQL;

-- 动态生成插入语句并执行
DECLARE @InsertSQL NVARCHAR(MAX);
SELECT @InsertSQL = N'INSERT INTO #TempData (' + @Columns + ')
SELECT ' + STRING_AGG('Row.value(''(' + c.value('local-name(.)', 'NVARCHAR(128)') + ')[1]'', ''NVARCHAR(MAX)'')', ', ') + '
FROM @XmlData.nodes(''/LocationsData/Row'') AS t(Row);';

EXEC sp_executesql @InsertSQL, N'@XmlData XML', @XmlData;

-- 查看结果
SELECT * FROM #TempData;

补充说明 #

• 列数据类型默认使用NVARCHAR(MAX)，如果需要更精准的类型（比如数字、日期），可以在提取列名时额外判断元素值的格式，再调整建表语句中的数据类型
• 临时表#TempData仅在当前会话有效，会话结束后自动销毁
• 若使用SQL Server 2016及以下版本（无STRING_AGG函数），可替换为FOR XML PATH拼接字符串的方式：

  -- 低版本提取列名
  SELECT @Columns = STUFF((SELECT ', ' + QUOTENAME(c.value('local-name(.)', 'NVARCHAR(128)'))
  FROM @XmlData.nodes('/LocationsData/Row[1]/*') AS t(c)
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
  
  -- 低版本生成插入语句
  SELECT @InsertSQL = N'INSERT INTO #TempData (' + @Columns + ')
  SELECT ' + 
  STUFF((SELECT ', Row.value(''(' + c.value('local-name(.)', 'NVARCHAR(128)') + ')[1]'', ''NVARCHAR(MAX)'')'
  FROM @XmlData.nodes('/LocationsData/Row[1]/*') AS t(c)
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '
  FROM @XmlData.nodes(''/LocationsData/Row'') AS t(Row);';
  

内容的提问来源于stack exchange，提问作者just_a_beginer