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
3
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
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')
#3
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).
x0040值是临时表的内部名称。你会得到正常输出当您选择从一个常规表(所以....选项可以是创建一个临时表,选择您想要的,并从db中删除它)。
#1
3
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
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')
#3
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).
x0040值是临时表的内部名称。你会得到正常输出当您选择从一个常规表(所以....选项可以是创建一个临时表,选择您想要的,并从db中删除它)。