使用 SQL Server XML 导入添加索引

Question

在SQL Server中，我尝试使用XML函数导入文件，如下所示：

SELECT 
file.value('@registrationnumber[1]', 'nvarchar(100)') AS [registrationnumber],
report.value('@nihil[1]', 'nvarchar(100)') AS [nihil],
report.value('@period[1]', 'nvarchar(100)') AS [period],
report.value('@formulaid[1]', 'nvarchar(100)') AS [formulaid],
report.value('@frequention[1]', 'nvarchar(100)') AS [frequention],
report.value('@versieon[1]', 'nvarchar(100)') AS [version],
variant.value('@type[1]', 'nvarchar(100)') AS [varianttype],
variant.value('@value[1]', 'nvarchar(100)') AS [variantvalue],
post.value('@cube[1]', 'nvarchar(100)') AS [cube],
post.value('@row[1]', 'nvarchar(100)') AS [row],
post.value('@rownr[1]', 'nvarchar(100)') AS [rownr],
INTO
[#parsed]
FROM
@xmlData.nodes('file') AS a(file)
CROSS APPLY
file.nodes('report') AS b(report)
CROSS APPLY
report.nodes ('post') AS c(post)
OUTER APPLY
report.nodes ('variant') AS d(variant)

我想知道是否可以为每个加载的报表自动添加一个带有唯一索引的附加列。

我无法在谷歌上找到解决方案，因为我认为我使用了错误的搜索词。

Answer 1

使用 ROW_NUMBER((：

SELECT 
file.value('@registrationnumber[1]', 'nvarchar(100)') AS [registrationnumber],
report.value('@nihil[1]', 'nvarchar(100)') AS [nihil],
report.value('@period[1]', 'nvarchar(100)') AS [period],
report.value('@formulaid[1]', 'nvarchar(100)') AS [formulaid],
report.value('@frequention[1]', 'nvarchar(100)') AS [frequention],
report.value('@versieon[1]', 'nvarchar(100)') AS [version],
variant.value('@type[1]', 'nvarchar(100)') AS [varianttype],
variant.value('@value[1]', 'nvarchar(100)') AS [variantvalue],
post.value('@cube[1]', 'nvarchar(100)') AS [cube],
post.value('@row[1]', 'nvarchar(100)') AS [row],
post.value('@rownr[1]', 'nvarchar(100)') AS [rownr],
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) YourSequentialIndex
INTO
[#parsed]
FROM
@xmlData.nodes('file') AS a(file)
CROSS APPLY
file.nodes('report') AS b(report)
CROSS APPLY
report.nodes ('post') AS c(post)
OUTER APPLY
report.nodes ('variant') AS d(variant)