none
How to assign the result of 'FOR XML PATH' to a variable? Impossible? RRS feed

  • Question

  • I want to get a comma delimited list of columns from a table. So I have a query like this.

    SELECT COLUMN_NAME + ','
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    FOR XML PATH('')

    I want to assign the result of this query to some varchar variable, but when I do I get this error. Are there any work arounds? The reason why I want to do this is because I want to dynamically generate an insert statement parameterized by a table name. If I can somehow contain the column list in a variable then the building of the insert statement is easy. Otherwsie, another way to do it would be to put the column names into a temp table and loop through that table, picking off the columns and build up the insert statement.

    The FOR XML clause is not allowed in a ASSIGNMENT statement.

    Wednesday, June 20, 2012 9:37 PM

Answers

  • Hi,

    It is certainly possible, try this code

    declare @varcols varchar(max);
    select @varcols=stuff((SELECT ','+COLUMN_NAME  
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @tablename
    FOR XML PATH('')),1,1,'')
    select @varcols

    Regards
    satheesh

    Friday, June 22, 2012 12:40 PM
  • Why is it not guaranteed to work? If you have a NULL in the list? Ok I get that, if that is what you are thinking, but in this case, I won't have NULL values in COLUMN_NAME.

    There is no correct behaviour for this type of expression. You may get a concatenated list. You may get something else.

    See this KB article:
    http://support.microsoft.com/default.aspx?scid=287515


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 21, 2012 10:05 PM

All replies

  • Well, didn't find the answer to this question, but did find a work around.

    DECLARE @col_list VARCHAR(2000); SET @col_list = '['
    SELECT @col_list = @col_list + sc.Column_Name+'],['
    FROM NFORMATION_SCHEMA.COLUMNS sc 
    WHERE Table_Name = 'Whatever_Table'
    ORDER BY Ordinal_Position
    SELECT @col_list = LEFT(@col_list,LEN(@col_list)-2)
    SELECT @col_list

    Wednesday, June 20, 2012 9:44 PM
  • Why you can't? try this:

    Declare @columns varchar(max);
    Declare @TableName sysname;
    Set @TableName = 'your_Table'
    
    Set @columns = ( SELECT COLUMN_NAME + ','
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    FOR XML PATH(''))
    
    Select @Columns


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Wednesday, June 20, 2012 9:48 PM
  • You can do it the following way:

    DECLARE @Table sysname = 'Product'
    DECLARE @QResult nvarchar(max) = ( SELECT COLUMN_NAME + ','
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = @Table
      FOR XML PATH(''))
    PRINT @QResult

    Dynamic SQL article:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016


    Wednesday, June 20, 2012 9:48 PM
    Moderator
  • If I understood you right, this is what you need:

    DECLARE @P VARCHAR(MAX)
    
    SELECT @P = STUFF((SELECT  ','+ COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'YourTableNameHere'
    FOR XML PATH('')),1,1,'')
    
    SELECT @P 




    Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.


    Wednesday, June 20, 2012 9:49 PM
  • But it's not a good workaround - that is not guaranteed to work.

    The other posts should set you straight.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 20, 2012 10:21 PM
  • That works. I was doing select @var = column_name ....

    Not set @var = (select column_name)

    Thursday, June 21, 2012 12:56 PM
  • But it's not a good workaround - that is not guaranteed to work.

    The other posts should set you straight.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Why is it not guaranteed to work? If you have a NULL in the list? Ok I get that, if that is what you are thinking, but in this case, I won't have NULL values in COLUMN_NAME.
    Thursday, June 21, 2012 12:57 PM
  • Because it uses undocumented feature of SQL Server. Also, with this approach you can not guarantee the ORDER BY.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 21, 2012 1:24 PM
    Moderator
  • Why is it not guaranteed to work? If you have a NULL in the list? Ok I get that, if that is what you are thinking, but in this case, I won't have NULL values in COLUMN_NAME.

    There is no correct behaviour for this type of expression. You may get a concatenated list. You may get something else.

    See this KB article:
    http://support.microsoft.com/default.aspx?scid=287515


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 21, 2012 10:05 PM
  • Hi,

    It is certainly possible, try this code

    declare @varcols varchar(max);
    select @varcols=stuff((SELECT ','+COLUMN_NAME  
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @tablename
    FOR XML PATH('')),1,1,'')
    select @varcols

    Regards
    satheesh

    Friday, June 22, 2012 12:40 PM