如何在TSQL中输出带有属性和字段值的xml

Question

我想以xml格式输出表中的一些字段。我需要将列的名称放在name-attribute中。输出必须类似于：

<udfields>
<udfield name="infoplanner">Test for the info</udfield>
<udfield name="plant">Amsterdam</udfield>
<udfield name="capability"/>
</udfields>

我试过这样的东西：

SELECT TOP 3
    ko.infoPlanner AS "udfield/name/@infoplanner"
  , ko.plant AS "udfield/name/@plant"
  , ko.capability AS "udfield/name/@capability"
FROM    sometable ko
WHERE   CONVERT(VARCHAR(8), ko.datumtijd, 112) = CONVERT(VARCHAR(8), GETDATE(), 112)
        AND COALESCE(ko.infoPlanner, '') <> ''
FOR     XML PATH('')

提前谢谢你，里昂

Answer 1

我猜你正在寻找类似这样的东西：

declare @T table
(
  infoplanner int,
  plant int,
  capability int
)

insert into @T
select 1, 2, 3 union all
select 10, 20 ,30

select (select 'infoplanner' as '@name', 
               infoplanner as '*' 
        for xml path('udfield'), type),
       (select 'plant' as '@name',
               plant as '*' 
        for xml path('udfield'), type),
       (select 'capability' as '@name',
               capability as '*' 
        for xml path('udfield'), type)
from @T
for xml path('udfields')

结果：

<udfields>
  <udfield name="infoplanner">1</udfield>
  <udfield name="plant">2</udfield>
  <udfield name="capability">3</udfield>
</udfields>
<udfields>
  <udfield name="infoplanner">10</udfield>
  <udfield name="plant">20</udfield>
  <udfield name="capability">30</udfield>
</udfields>