本文介绍了如何在TSQL中输出带有属性和字段值的xml的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
限时送ChatGPT账号..我想从 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屋!
更多推荐
[db:关键词]
发布评论