SET more than 1 row output into 1 variable

Question

I am wanting to be able to save more than just 1 output from a SELECT query in a single variable.

Currently I am gathering my needed data like so:

DECLARE @something1 VARCHAR(MAX)
DECLARE @something2 VARCHAR(MAX)
DECLARE @something3 VARCHAR(MAX)

SET @something1 = (
  SELECT 
     Custom AS 'XXL Format' 
  FROM 
     tblData 
  WHERE 
     ID = 1);

SET @something2 = (
  SELECT 
     Custom.value('(/Individual/text())[1]', 'varchar(MAX)') AS 'Non XML Format' 
  FROM 
     tblData 
  WHERE 
     ID = 1)

SET @something3 = (
  SELECT 
     tbl1.paper, 
     tbl2.type 
  FROM 
     tblData AS tbl1 
  JOIN tblData2 tbl2 
     ON tbl1.ID = tbl2.ID 
  WHERE 
     ID = 1);

I have the following demo that shows what I am wanting to do

DECLARE @tester VARCHAR(MAX)

SET @tester = (
  SELECT 
     tbl1.Custom AS 'XXL Format',
     tbl1.Custom.value('(/Individual/text())[1]', 'varchar(MAX)') AS 'Non XML Format'
     tbl1.paper, 
     tbl2.type 
  FROM 
     tblData AS tbl1 
  JOIN tblData2 tbl2 
     ON tbl1.ID = tbl2.ID 
  WHERE 
     ID = 1);

I get the error of:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Both demos can be found here

I have tried to set the variable to "table" and store the data like that but that also does not seem to work correctly [I'm sure I am doing something wrong - that may be the answer to this question I'm asking]

How can I just use 1 variable for all that the above query outputs?

Answer 1

So I guess I was correct with the set the variable to a temp "table". I finally just now got it to work for my needs!

DECLARE @tmpTbl table (_xml, _parsedXML, _paper, _type)

INSERT INTO @tmpTbl 
  SELECT 
     tbl1.Custom AS 'XML Format',
     tbl1.Custom.value('(/Individual/text())[1]', 'varchar(MAX)') AS 'Non XML Format',
     tbl1.paper, 
     tbl2.type  
  FROM 
     tblData AS tbl1 
  JOIN tblData2 AS tbl2 
     ON tbl1.ID = tbl2.ID 
  WHERE 
     ID = 1;

DECLARE @something1 VARCHAR(MAX) = (SELECT _xml FROM @tmpTbl);
DECLARE @something2 VARCHAR(MAX) = (SELECT _parsedXML FROM @tmpTbl);
DECLARE @something3 VARCHAR(MAX) = (SELECT _paper FROM @tmpTbl);
DECLARE @something4 VARCHAR(MAX) = (SELECT _type FROM @tmpTbl);

DELETE FROM @tmpTbl --Not really needed but nice to be offical :)

This above stores the values into one place. Although its not inside 1 variable I guess having to create a temp table isn't all that bad for the database/performance...