XML数据类型方法“值”的参数1必须是字符串文字 Download

下面是我的查询

DECLARE @Error_Description NVARCHAR(Max)
DECLARE @Count VARCHAR(20)
DECLARE @x NVARCHAR(Max)

SELECT @Error_Description = 'The external columns for Excel Source are out of synchronization with the data source columns. 
The column "szReferencceNumber" needs to be added to the external columns.
The column "SMSa" needs to be added to the external columns.
The column "as" needs to be added to the external columns.'

SELECT @Count = (LEN(@Error_Description) - LEN(REPLACE(@Error_Description, '"', ''))) / LEN('"')

SELECT @Count

SELECT COALESCE(LTRIM(CAST(('<X>' + REPLACE(@Error_Description, '"', '</X><X>') + '</X>') AS XML).value('(/X)[' + @Count + ']', 'varchar(128)')), '')

2 个答案:

答案 0 :(得分：0)

value的第一个参数必须是字符串文字。要选择具有动态索引的节点，您可以执行以下操作

SELECT 
n.value('.', 'varchar(128)') as Result
from (SELECT CAST(('<X>' + REPLACE(@Error_Description, '"', '</X><X>') + '</X>') AS XML)) ca(x)
CROSS APPLY x.nodes('(/X)') n(n)
WHERE n.value('for $l in . return count(../*[. << $l]) + 1', 'int') %2 = 0

这将返回第二个节点的值。因此，获得用引号引起来的值可以达到预期的结果。

Result
---------------------
szReferencceNumber
SMSa
as

答案 1 :(得分：0)

如果您使用的是2012+，并且可以使用nvarchar(4000)（而不是MAX)，则可以获取DelimitedSplitN4K_LEAD的副本并获取{{1} }甚至：

ItemNumber

如果您使用的是SQL Server 2016+，则可以使用一些JSON操作（支持DECLARE @Error_Description nvarchar(4000); SELECT @Error_Description = N'The external columns for Excel Source are out of synchronization with the data source columns. The column "szReferencceNumber" needs to be added to the external columns. The column "SMSa" needs to be added to the external columns. The column "as" needs to be added to the external columns.'; SELECT DS.Item FROM dbo.DelimitedSplitN4K_LEAD(@Error_Description,'"') DS WHERE DS.ItemNumber % 2 = 0; 值）：

MAX