Format Table Variable Output with FOR XML AUTO

Question

Using SQL Server 2008.

I have a table variable with a single column and single row.

If I do this:

Declare @testsToRun Table ( testsId BigInt )
Insert Into @testsToRun
Select testsId From tests Where testsId = 10

Select Top 1 * From @testsToRun
For Xml Auto , Type , Root('testMessage') 

I get XML that looks like this:

<testMessage> 
    <_x0040_testsToRun testsId="10" />
</testMessage>

When what I actually want is:

<testMessage>
    <testsToRun testsId="10" />
</testMessage>

If the row source is a table, that seems to work fine. When it is a table variable I get a child element label I don't want, I want testsToRun and not _x0040_testsToRun.

How can I rework my FOR XML statement/clause to give me proper output?

Thanks.

Answer 1

Try using an alias on the temp table:

Declare @testsToRun Table ( testsId BigInt )
Insert Into @testsToRun
Select testsId From tests Where testsId = 10

Select Top 1 * From @testsToRun testsToRun
For Xml Auto , Type , Root('testMessage') 

Answer 2

The x0040 value is the internal name for the temporary table. You will get normal output when you select from a regular table (so.... the option could be to create a temp table, select what you want and drop it from the db).

Answer 3

Try this instead - use FOR XML PATH and define your output structure with the column alias(ses) you use:

SELECT TOP 1
    testsId AS '@testsId'
FROM 
    @testsToRun
FOR XML PATH('testsToRun'), ROOT('testMessage') 

Gives me:

<testMessage>
  <testsToRun testsId="10" />
</testMessage>