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.

marc_s · Accepted Answer · 2010-07-27 21:03:35Z

4

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>

answered Jul 27, 2010 at 21:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Snowy Over a year ago

While this changes my stuff from attribute-based to element based (making the whole message larger), it was easy enough to change my message reader code. This works great. Thanks!

marc_s Over a year ago

@ScSub: how does this change to element-based?? It gives you testsId as an attribute - just like you specified in your original post....

kniemczak · Accepted Answer · 2010-07-27 20:48:31Z

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')

answered Jul 27, 2010 at 20:48

kniemczak

3591 silver badge4 bronze badges

Comments

riffnl · Accepted Answer · 2010-07-27 20:48:36Z

0

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).

answered Jul 27, 2010 at 20:48

riffnl

3,1911 gold badge21 silver badges33 bronze badges

Collectives™ on Stack Overflow

Format Table Variable Output with FOR XML AUTO

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related