Need help on following:
@strSql is a dynamic query in while loop which can return single record, single row of records, multiple rows of records.
So only if it returns single record then I have to store it otherwise convert to xml before storing.

1) If it returns 1 record(1row and 1 column) then save as it is.
2) if it returns a row with more than 1 columns then convert to xml before saving.
2) if it returns data rows then convert to xml before saving.

I think you choose whether to save /convert to XML before saving depending on whether the query returns a result set with single column / multiple columns ( you've mentioned that if query returns 1 row with multiple columns or it returns multiple rows, then save to XML)

1) "If it returns 1 record(1row and 1 column) then save as it is."

Since the query is prepared dynamically based on some logic, won't you know whether it will return a single column??? I assume you are not using SELECT *

Please post the prepared dynamic sql

the query is as below.. dynamic query is taken from some other table.. Please suggest what logic can be applied before deciding on to convert to xml.. I am not able to find the solution..
DECLARE @istrType VARCHAR(8);
DECLARE @strSQL NVARCHAR(MAX);
DECLARE @strParam VARCHAR(MAX);
DECLARE @intRows INT;
DECLARE @intLoop INT;
DECLARE @ItemCode VARCHAR(50);

SET @istrType='daily';
DECLARE @tblQuery TABLE
(RunId INT IDENTITY(1,1),
ID VARCHAR(20),
ItemCode VARCHAR(50),
Query VARCHAR(MAX));
INSERT INTO @tblQuery (ID, ItemCode, Query)
SELECT ID, VariableCode, SelectQry FROM [dbo].[Tb_ReportQuery];
SELECT @intRows = COUNT(*) FROM @tblQuery;
SET @intLoop = 1;

WHILE @intLoop <= @intRows
BEGIN
SELECT @ItemCode = ItemCode FROM @tblQuery WHERE RunId = @intLoop;
SELECT @strSQL = Query FROM @tblQuery WHERE RunId = @intLoop;

IF(Output of @strSQL returns more than 1 record)
BEGIN
SELECT @strSQL = '(' + @strSQL + ' FOR XML PATH (''Element''), ROOT(''Root''))';
END
ELSE
BEGIN
SELECT @strSQL = '(' + @strSQL + ')';
END
SET @strSQL = 'Insert Into Tb_Process_Values
(CurDate, Code, ReportType, Value)
Select ''' + GETDATE() + ''',''' + @ItemCode + ''',''' + @istrType + ''',' + @strSQL;
EXEC (@strSQL);
SET @intLoop = @intLoop + 1;
END