Mar 17, 2014

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.


If we can use FOR XML AUTO on a SELECT statement which doesn't use any table to select any column. For example :

SELECT 1 FOR XML AUTO

Or

SELECT
    'ExactHelp',
    (SELECT TOP(1) Stu_Name FROM Student)
FOR XML AUTO

We will get error message like:

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.

Solution:

1. Write above sql query like this:

SELECT *
FROM (SELECT 1 AS Col) AS SubQuery
FOR XML AUTO

SELECT * FROM(
    SELECT 'ExactHelp' AS Col1,
    (SELECT TOP(1) Stu_Name FROM Student) AS Col2
) AS tblApp
FOR XML AUTO

2. Instead of FOR XML AUTO use FOR XML RAW

SELECT 1 AS Col FOR XML RAW

SELECT
    'ExactHelp' AS Col1,
    (SELECT TOP(1) Stu_Name FROM Student) AS Col2
FOR XML RAW

No comments:

Post a Comment