使用XML AUTO格式化表变量输出

Using SQL Server 2008.

使用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:

我得到的XML是这样的:

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

如果行源是一个表，这似乎可以正常工作。当它是一个表变量时，我得到一个我不想要的子元素标签，我想要testsToRun而不是_x0040_testsToRun。

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

如何重做FOR XML语句/子句以获得适当的输出?

Thanks.

谢谢。

3 个解决方案

#1

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

试试这个方法——使用XML路径并使用列别名定义输出结构:

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

Gives me:

给我:

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

#2

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

#3

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

x0040值是临时表的内部名称。你会得到正常输出当您选择从一个常规表(所以....选项可以是创建一个临时表，选择您想要的，并从db中删除它)。

#1