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

1 comment:

  1. Managing up the big data is not easier, but it become too complex for you if someone asked you to convert this big data into a graphical format so here your thinking ability is stop. For giving this task done let your work on data scientist which can easily convert the big data into short and graphical form, and you get the data scientist form this https://activewizards.com/.

    ReplyDelete