Apr 17, 2013

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

No comments:

Post a Comment