0
DECLARE @v NVARCHAR(MAX)=''
select @v =STUFF((SELECT CASE WHEN MeterID=0 THEN '' ELSE CONVERT(VARCHAR(50),MeterID) + ',' END AS [text()] 
                        FROM (SELECT DISTINCT mr.MeterID FROM #FilteredMeterIDs mr WHERE mr.apartmentID = fmr.apartmentID)m
                        FOR XML PATH,TYPE).value('(.)[1]','NVARCHAR(MAX)'),1,0,'')
                    FROM #FilteredMeterIDs fmr
select @v

Result: 181834,

BUT when i use below query , it gives NULL as RESULT,

DECLARE @v NVARCHAR(MAX)=''
select @v =STUFF((SELECT CASE WHEN ISNULL(MeterID,0)=0 THEN '' ELSE CONVERT(VARCHAR(50),MeterID) + ',' END AS [text()] 
                        FROM (SELECT DISTINCT mr.MeterID FROM #FilteredMeterIDs mr WHERE mr.apartmentID = fmr.apartmentID)m
                        FOR XML PATH,TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,0,'')
                    FROM #FilteredMeterIDs fmr
select @v

Result: NULL

i have used "/text()" for improving performance. What is the issue, can anyone tell me ?!

  • Can you provide sample data? The most likely issue is you're not specifying the path for the text() call. See this fiddle for an example. – HandyD Jan 20 at 22:53
  • i have tried FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'). and got the expected solution. – Arun Kumar Jan 21 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.