TSQL Union in sub query

I have a table called Employee.emp.Language that contains a list of languages to choose from on the UI. I am unable to add a record to this table and unable to edit the UI directly to solve my problem.

I need to add an option to my query that adds NULL into my result set. This essentially will allow a user to pick null from the drop-down list.

  • In SQL server is there any way to get the 'use database' command to accept a variable
  • How to create transaction for commands that not written in the same field of event or method
  • Insert values into identity column manually and automatically in one query
  • Taking a value from a Stored Procedure
  • Use local database from master in MS SQLEXPRESS
  • Could not generate script from SQL Azure
  • DECLARE @nulls AS TABLE (fieldLabel VARCHAR(10) NULL, fieldValue VARCHAR(10) 
    INSERT INTO @nulls( fieldLabel, fieldValue )
            SELECT  LanguageName AS FieldLabel,
                    LanguageID AS FieldValue
            FROM    Employee.emp.Language
            WHERE  IsRetired = 0
            SELECT fieldLabel ,
                FROM @nulls
        FOR    XML PATH ('options'), ELEMENTS, TYPE, ROOT ('languages') 

    While my list contains the null option to select in my dropdown, I am having trouble getting this piece of code to save into my stored procedure.

    The error I am getting is: The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

    As soon as I remove the union and the select following it, all works fine.

    How else could I accomplish adding a value to the result set without adding it to the table directly?

    2 Solutions collect form web for “TSQL Union in sub query”

    Use it as a subquery, and you don’t need a table valued, you can use a select with null values.

    create table lang(fieldlabel varchar(10), fieldvalue varchar(20));
    insert into lang values 
    ('en', 'english'),
    ('fr', 'french');
    select fieldlabel, fieldvalue
    from (select fieldlabel, fieldvalue
          from   lang
          union all
          select null as fieldlabel, null as fieldvalue) l
    FOR    XML PATH ('options'), ELEMENTS, TYPE, ROOT ('languages')
    | (No column name)                                                                                                                                                                              |
    | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
    | <languages><options><fieldlabel>en</fieldlabel><fieldvalue>english</fieldvalue></options><options><fieldlabel>fr</fieldlabel><fieldvalue>french</fieldvalue></options><options /></languages> |

    dbfiddle here

    Try inserting the null values directly instead of using a separate table. Try:


    From How to use union all with manual value (not from another tabel)?

    Note: Edited answer

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.