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.

  • How to traverse a path in a table with id & parentId?
  • Subtract value to multiple rows - SQL
  • Loop Join in SQL Server 2008
  • Windows Azure SQL Database - Identity Auto increment column skips values
  • Real life example, when to use OUTER / CROSS APPLY in SQL
  • SQL Server, can't insert null into primary key field?
  • DECLARE @nulls AS TABLE (fieldLabel VARCHAR(10) NULL, fieldValue VARCHAR(10) 
    
    NULL)
    INSERT INTO @nulls( fieldLabel, fieldValue )
    VALUES  ('NULL', NULL )
    
    SELECT 
    (
            SELECT  LanguageName AS FieldLabel,
                    LanguageID AS FieldValue
            FROM    Employee.emp.Language
            WHERE  IsRetired = 0
            UNION
            SELECT fieldLabel ,
                    fieldValue
                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')
    GO
    
    | (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:

    UNION ALL SELECT 'PCS','LTR','1'
    

    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.