SQL - 如何为 XML PATH 提供包含动态列的参数

Question

有4列（journeyId，journeydate，elementid，channelid），但我只想在选择XML路径中包含具有值的列（journeyid是必须具有值的）。但是我没有将值设置为 xml 格式，而是只获得了列名作为结果。

Declare @JourneyDate datetime='2019-01-22 12:15:36.380'
Declare @ElementID varchar(50)=''
Declare @ChannelID varchar(50)=''
Declare @JourneyID varchar(50)='58597F9C-C468-4E30-A481-3B21170F9E63'
Declare @Selects varchar(2000)='JourneyID'
Declare @Addstate xml
Declare @sql nvarchar(max)
if @JourneyDate is not null
begin
set @Selects = @Selects + ',JourneyDate'
end 
if isnull(@ElementID,'')<>''
begin
set @Selects = @Selects + ',ElementID'
end 
if isnull(@ChannelID,'')<>''
begin
set @Selects = @Selects + ',ChannelID'
end 

set @Addstate=(Select @Selects
               FROM [tbl_Journey]
               where JourneyID= @JourneyID
               FOR XML PATH('root'))
select @Addstate

Result:
<root>JourneyID,JourneyDate</root>
But what I need is below results:
<root><JourneyID>58597F9C-C468-4E30-A481-3B21170F9E63</JourneyID><JourneyDate>2019-01-22T12:15:36.380</JourneyDate></root>

Answer 1

生成并执行动态语句。使用语句时，将为表中的每一行选择@Selects变量的值，而不是列值。

...
DECLARE @stm nvarchar(max)
SET @stm = N'
    SET @Addstate = (
            SELECT ' + @Selects + N' FROM [tbl_Journey] 
            WHERE JourneyID = @JourneyID
            FOR XML PATH(''root'')
    )
'
EXEC sp_executesql @stm, N'@Addstate xml OUTPUT, @JourneyID varchar(50)', @Addstate OUTPUT, @JourneyID
SELECT @Addstate

Answer 2

xml

应该动态形成，如下所示，这会将变量值添加到 xml 中

if @JourneyDate is not null
begin
set @Selects = @Selects + ','+CAST(@JourneyDate AS NVARCHAR(255))
end 
if isnull(@ElementID,'')<>''
begin
set @Selects = @Selects + ','+@ElementID
end 
if isnull(@ChannelID,'')<>''
begin
set @Selects = @Selects + ','+@ChannelID
end