如何在TSQL中输出带有属性和字段值的xml [英] how to output xml with attribute and field value in TSQL

问题描述

我想从 xml 中的表中输出一些字段.我需要将列的名称放在名称属性中.输出必须类似于:

I'd like to output some fields from a table in xml. I need to put the name of the column in the name-attribute. The output must resemble:

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

提前致谢，里昂

推荐答案

我猜你正在寻找这样的东西:

I guess you are looking for something like this:

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>

这篇关于如何在TSQL中输出带有属性和字段值的xml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！