XML路径的内容不能连接位类型

Question

我有一个模拟布尔值的位类型的字段，但是我试图使用STUFF FOR XML PATH将字段连接到一个字符串字段中，它一直给我一个错误。以下是查询：

select
    ui.IndivId,
    ui.correctedInFNV,
    ni.firstName,
    ni.prefName,
    ni.lastName,
    ni.suffix,
    ni.alreadyCorrect as nameCorrect,
    ni.updated as nameUpdated,
    ni.updatedDate,
    ni.correctPerson,
    ai.addr1,
    ai.addr2,
    ai.addr3,
    ai.city,
    ai.state,
    ai.zip,
    ai.country,
    ai.alreadycorrect as adcorrect,
    ai.updatedDate,
    ai.updated as adupdated,
    pi.priPhoneType,
    pi.priPhoneNumber,
    pi.updated as phoneupdated,
    pi.updateddate,
    ei.priEmailType,
    ei.priEmailAddress,
    ei.alreadycorrect as emailcorrect,
    ei.updated as emailupdated,
    STUFF((SELECT ','+pos.positionTitle from positionInfo as pos where pos.IndivId=ui.IndivId FOR XML PATH('')),1,1,'') AS POS_TITLE,
    STUFF((SELECT ','+pos.positionId from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_ID,
    STUFF((SELECT ','+pos.positionCode from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_CODE,
    STUFF((SELECT ','+pos.areaServed from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_AREA,
    STUFF((SELECT ','+pos.districtServed from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_DIST,
    STUFF((SELECT ','+pos.reason from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_REASON,
    STUFF((SELECT ','+pos.currentlyHeld from positionInfo as pos where pos.IndivId=ui.IndivId FOR XML PATH('')),1,1,'') AS POS_HELD
from 
    updatedInfo as ui
    join nameInfo as ni on ui.IndivId=ni.nameInfoId
    join addressInfo as ai on ui.IndivId=ai.addressInfoId
    join phoneInfo as pi on ui.IndivId=pi.phoneInfoId
    join emailInfo as ei on ui.IndivId=ei.emailInfoId
    JOIN positionInfo AS pos ON ui.IndivId=pos.IndivId

当运行时，我会得到错误。

Msg 402、级别16、状态1、第36行数据类型varchar和位在add运算符中不兼容。

它在为STUFF的最后一条pos.currentlyHeld语句尖叫。因此，我尝试将字段转换为char类型，如下所示：

STUFF((SELECT ','+CAST(pos.currentlyHeld AS VARCHAR(1)) FROM positionInfo as pos where pos.IndivId=ui.IndivId FOR XML PATH('')),1,1,'') AS POS_HELD

然后它就因为某个逗号而对我尖叫？不确定。

将varchar值“，”转换为数据类型int时，Msg 245、级别16、状态1、第1行转换失败。

语法问题在哪里？或者这是更多的数据类型问题？

Answer 1

考虑到这个问题是针对Server 2008实现的，因此CONCAT()函数不可用。

正如建议的那样，查询应该在每个NVARCHAR(MAX)语句中将所有字段转换为STUFF。

最后的查询如下：

select distinct
    ui.IndivId,
    ui.correctedInFNV,
    ni.firstName,
    ni.prefName,
    ni.lastName,
    ni.suffix,
    ni.alreadyCorrect as nameCorrect,
    ni.updated as nameUpdated,
    ni.updatedDate,
    ni.correctPerson,
    ai.addr1,
    ai.addr2,
    ai.addr3,
    ai.city,
    ai.state,
    ai.zip,
    ai.country,
    ai.alreadycorrect as adcorrect,
    ai.updatedDate,
    ai.updated as adupdated,
    pi.priPhoneType,
    pi.priPhoneNumber,
    pi.updated as phoneupdated,
    pi.updateddate,
    ei.priEmailType,
    ei.priEmailAddress,
    ei.alreadycorrect as emailcorrect,
    ei.updated as emailupdated,
    STUFF((SELECT ','+CONVERT(NVARCHAR(MAX),pos.positionTitle) from positionInfo as pos where pos.IndivId=ui.IndivId FOR XML PATH('')),1,1,'') AS POS_TITLE,
    STUFF((SELECT ','+CONVERT(NVARCHAR(MAX),pos.positionId) from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_ID,
    STUFF((SELECT ','+CONVERT(NVARCHAR(MAX),pos.positionCode) from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_CODE,
    STUFF((SELECT ','+CONVERT(NVARCHAR(MAX),pos.areaServed) from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_AREA,
    STUFF((SELECT ','+CONVERT(NVARCHAR(MAX),pos.districtServed) from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_DIST,
    STUFF((SELECT ','+CONVERT(NVARCHAR(MAX),pos.reason) from positionInfo as pos where pos.IndivId=ui.IndivId for XML PATH('')),1,1,'') as POS_REASON,
    STUFF((SELECT ',' + CONVERT(NVARCHAR(MAX), pos.currentlyHeld) from positionInfo as pos where pos.IndivId=ui.IndivId FOR XML PATH('')),1,1,'') AS POS_HELD
from 
    updatedInfo as ui
    join nameInfo as ni on ui.IndivId=ni.nameInfoId
    join addressInfo as ai on ui.IndivId=ai.addressInfoId
    join phoneInfo as pi on ui.IndivId=pi.phoneInfoId
    join emailInfo as ei on ui.IndivId=ei.emailInfoId
    JOIN positionInfo AS pos ON ui.IndivId=pos.IndivId

DISTINCT关键字用于防止PositionInfo表中的每个条目都有一行，这是为了将值填充到POS字段中。

感谢每一个为这个问题做出贡献的人。