从t-sql查询中编写特定结构化的XML

I would like to be able to write out an XML file in this specific format. I've been reading up on bcp and experimenting with FOR XML but can't seem to achieve what I need here. I would appreciate any help in the right direction. While I know I could refactor the code that would consume this XML output; I'd like to be adventurous here and stick to the task at hand without changing anything.

我希望能够以这种特定格式写出XML文件。我一直在阅读bcp并尝试使用FOR XML,但似乎无法达到我的需求。我希望在正确的方向上提供任何帮助。虽然我知道我可以重构将消耗此XML输出的代码;我想在这里冒险,坚持手头的任务,不做任何改变。

SQL code for recreating a temp var table dataset

用于重新创建临时变量表​​数据集的SQL代码

declare @dataset table(Color nvarchar(10), Number int, Code nvarchar(10))

insert into @dataset 
  select 'Green', 12345, 'US1'
  union
  select 'Red', 56789, 'US2'

select * from @dataset

And from this query I would like to generate the following XML document.

从这个查询我想生成以下XML文档。

<?xml version="1.0" encoding="utf-8" ?>
<test>
  <collection>
    <Case>
      <input>
        <attribute name="Color">Green</attribute>
        <attribute name="Number">12345</attribute>
        <attribute name="Code">US1</attribute>
      </input>
    </Case>
    <Case>
      <input>
        <attribute name="Color">Red</attribute>
        <attribute name="Number">56789</attribute>
        <attribute name="Code">US2</attribute>
      </input>
    </Case>
  </collection>
</test>

I will defer to you experts to tell me if this is too ridiculous to be accomplished, but I think it "is" possible as I've got a little close so far.

我会告诉你专家告诉我这是否太荒谬无法完成,但我认为它“有可能”,因为我到目前为止已经有点接近了。

I've been tinkering with this and able to write out XML files.

我一直在修补它并且能够写出XML文件。

exec master..xp_cmdshell 'bcp "Query Here" queryout "c:\filename.xml" -c -T'

Thanks SO members!

谢谢SO成员!

1 个解决方案

#1

select (
       select (
              select 'Color' as [attribute/@name],
                     Color as [attribute],
                     null,
                     'Number' as [attribute/@name],
                     Number as [attribute],
                     null,
                     'Code' as [attribute/@name],
                     Code as [attribute]
              for xml path('input'), type 
              ) 
       from @dataset
       for xml path('Case'), root('collection'), type
       )
for xml path('test'), type