FOR XML EXPLICIT requires at least three columns, including the tag column, the parent column, and at least one data column sql server

I am creating a Employee table in sql server and inserting two records into it:

CREATE TABLE Employee(

    Id BIGINT IDENTITY PRIMARY KEY,

    Name VARCHAR(50),

    Location VARCHAR(20)

)

INSERT INTO Employee VALUES('Scott','USA'),('Greg','UK')

If we will execute following sql query :

SELECT Id, Name FROM Employee FOR XML EXPLICIT

We will get error message:

FOR XML AUTO requires at least one table for generating XML tags. Use FOR XML RAW or add a FROM clause with a table name.

Cause:  In correct syntax for using FOR XML EXPLICIT clause.

Solution:  Correct syntax is:

SELECT

    1 AS Tag,

    NULL AS Parent,

    Id AS [Employee!1!Id],

    NULL AS [Data!2!Name],

    NULL AS [Data!2!Location]

FROM Employee

UNION ALL

SELECT

    2,

    1,

    Id,

    Name ,

    Location 

FROM Employee

ORDER BY [Employee!1!Id]

 FOR XML EXPLICIT