SQL Server 2008 结果字符串替换 - 多标识符

Question

我目前坚持运行以下SQL查询：

SELECT 
    c.[DeviceName],
    STUFF((SELECT ' + ' +  sw.[SuiteName] FROM [dbo].[AppSoftwareSuites] as sw where sw.[Computer_Idn] = c.[Computer_Idn] AND sw.[SuiteName] like 'CFRM-Server%' FOR XML PATH('')),1,3,'') AS 'CFRM',
    STUFF((SELECT ' + ' +  sw.[SuiteName] FROM [dbo].[AppSoftwareSuites] as sw where sw.[Computer_Idn] = c.[Computer_Idn] AND sw.[SuiteName] like 'Communication Module%' FOR XML PATH('')),1,3,'') AS 'Communication Module',
    STUFF((SELECT ' + ' +  sw.[SuiteName] FROM [dbo].[AppSoftwareSuites] as sw where sw.[Computer_Idn] = c.[Computer_Idn] AND sw.[SuiteName] like 'GEBIT-Commons-Java%' FOR XML PATH('')),1,3,'') AS 'Gebit Commons Java',
    STUFF((SELECT ' + ' +  sw.[SuiteName] FROM [dbo].[AppSoftwareSuites] as sw where sw.[Computer_Idn] = c.[Computer_Idn] AND sw.[SuiteName] like 'GEBIT-Commons_JBOSS%' FOR XML PATH('')),1,3,'') AS 'Gebit Commons JBOSS',
    STUFF((SELECT ' + ' +  sw.[SuiteName] FROM [dbo].[AppSoftwareSuites] as sw where sw.[Computer_Idn] = c.[Computer_Idn] AND sw.[SuiteName] like 'Mobile Store%' FOR XML PATH('')),1,3,'') AS 'Mobile Store',
    STUFF((SELECT ' + ' +  sw.[SuiteName] FROM [dbo].[AppSoftwareSuites] as sw where sw.[Computer_Idn] = c.[Computer_Idn] AND sw.[SuiteName] like 'NEWPOSS-Store-Server%' FOR XML PATH('')),1,3,'') AS 'NEWPOSS',
    STUFF((SELECT ' + ' +  sw.[SuiteName] FROM [dbo].[AppSoftwareSuites] as sw where sw.[Computer_Idn] = c.[Computer_Idn] AND sw.[SuiteName] like 'Store Portal - Complete%' FOR XML PATH('')),1,3,'') AS 'Store Portal',
    STUFF((SELECT ' + ' +  sw.[SuiteName] FROM [dbo].[AppSoftwareSuites] as sw where sw.[Computer_Idn] = c.[Computer_Idn] AND sw.[SuiteName] like 'Store S&R Services%' FOR XML PATH('')),1,3,'') AS 'SSR'
FROM 
    [dbo].[Computer] as c WITH (NOLOCK)
WHERE
    c.[DeviceName] LIKE '%STL01'
ORDER BY 
    c.[DeviceName] ASC;

输出

很好，但似乎数据库中的一个产品(用于"SSR"(具有"&"字符，导致输出损坏("存储S&R服务"而不是"S&R Services xx.xx.xx"(。

有机会在输出中执行字符串替换吗？

"对于 XML"的原因是因为数据库中每个设备名称的重复条目导致结果为空。

任何帮助都值得赞赏:)

Answer 1

看看

这个：

DECLARE @teststring VARCHAR(100)='This is for test & show';
SELECT(SELECT ' + ' + @teststring FOR XML PATH(''));
SELECT((SELECT ' + ' + @teststring FOR XML PATH(''),TYPE).value('.','nvarchar(max)'));

两个结果是：

+ This is for test & show
+ This is for test & show

将FOR XML与禁止的字符(在大多数情况下为'<、>和&'(一起使用将导致转义实体。XML 只是不能使用这些字符，因为它们具有元语义。

还有更多的字符可能会导致麻烦，例如换行符或许多外国字符......

简单的解决方案：按照第二行所示包装SELECT ... FOR XML ...，并通过value()方法读取内部值。这会将实体隐式重新编码为以前的字符。