sql-server - FOR XML SQL Server - 输出 XML 中的变量元素名称

Question

我对 FOR XML 很陌生在 SQL Server 中，我进行了大量搜索，但找不到答案。

我能否使用“for xml”来获得可变元素名称，其中元素名称不是硬编码的，而是取自每行中的一个单元格？举个例子...

表格ORDERS :

ID     STATUS       TIME      AMOUNT
------------------------------------
1      COMPLETE     02:31     2355
2      ACCEPTED     02:39     6653
3      ACCEPTED     04:21     4102
4      RECEIVED     05:03     4225

FOR XML查询:

select ID,
       TIME as STATUS_TIME,
       AMOUNT as CURRENT_AMOUNT
from   ORDERS
for xml raw('  **STATUS NAME HERE**  '),root('ORDERS'), elements

要求的输出:

<ORDERS>
   <COMPLETE>               <<<<--- Variable element name from STATUS in ORDERS
       <ID>1</ID>
       <STATUS_TIME>02:31</STATUS_TIME>
       <CURRENT_AMOUNT>2355</CURRENT_AMOUNT>
   </COMPLETE>
   <ACCEPTED>               <<<<--- Variable element name from STATUS in ORDERS
       <ID>2</ID>
       <STATUS_TIME>02:39</STATUS_TIME>
       <CURRENT_AMOUNT>6653</CURRENT_AMOUNT>
   </ACCEPTED>
   <ACCEPTED>               <<<<--- Variable element name from STATUS in ORDERS
       <ID>3</ID>
       <STATUS_TIME>04:21</STATUS_TIME>
       <CURRENT_AMOUNT>4102</CURRENT_AMOUNT>
   </ACCEPTED>
   <RECEIVED>               <<<<--- Variable element name from STATUS in ORDERS
       <ID>4</ID>
       <STATUS_TIME>05:03</STATUS_TIME>
       <CURRENT_AMOUNT>4225</CURRENT_AMOUNT>
   </RECEIVED>
</ORDERS>

我知道我可以给元素名称赋予属性，我可以给个体 ORDER在ORDERS和 STATUS 的属性如下所示，但不幸的是，这不是将要接收 XML 文档的人所需要的 :(

select ID,
       STATUS as '@STATUS'
       TIME as STATUS_TIME,
       AMOUNT as CURRENT_AMOUNT
from   ORDERS
for xml raw('ORDER'),root('ORDERS'), elements

输出:

<ORDERS>
   <ORDER STATUS='COMPLETE'>                <<<<--- Attribute for STATUS but not what I want
       <ID>1</ID>
       <STATUS_TIME>02:31</STATUS_TIME>
       <CURRENT_AMOUNT>2355</CURRENT_AMOUNT>
   </ORDER>
   <ORDER STATUS='ACCEPTED'>               <<<<--- Attribute for STATUS but not what I want
       <ID>2</ID>
       <STATUS_TIME>02:39</STATUS_TIME>
       <CURRENT_AMOUNT>6653</CURRENT_AMOUNT>
   </ORDER>
....

如果可能的话，我希望能够在 SQL Server 中完成所有这些工作。 如果你能在这方面帮助我，非常非常感谢。

Answer 1

您不能在 XML Raw() 中指定列值。所以你要做的是从选择查询中选择所需的列并将结果转换为 XML，就像这样 -

架构

DECLARE @temp table (ID int, [STATUS] [varchar](100) NOT NULL, [TIME] [varchar](100), AMOUNT int);

INSERT @temp (ID, [STATUS], [TIME], AMOUNT) VALUES (1, 'COMPLETE', '02:31', 2355),(2, 'ACCEPTED', '02:41', 6653),(3, 'ACCEPTED', '02:31', 4102),(4, 'ACCEPTED', '02:31', 4225)

查询

SELECT 
CAST('<' + STATUS + '>' + 
    '<ID>' + CAST(ID AS varchar) + '</ID>' + 
    '<TIME>' + TIME + '</TIME>' + 
    '<AMOUNT>' + CAST(AMOUNT AS varchar) + '</AMOUNT>' + 
'</' + STATUS + '>' AS XML) from @temp
FOR XML PATH(''),root('ORDERS')

输出

<ORDERS>
  <COMPLETE>
    <ID>1</ID>
    <TIME>02:31</TIME>
    <AMOUNT>2355</AMOUNT>
  </COMPLETE>
  <ACCEPTED>
    <ID>2</ID>
    <TIME>02:41</TIME>
    <AMOUNT>6653</AMOUNT>
  </ACCEPTED>
  <ACCEPTED>
    <ID>3</ID>
    <TIME>02:31</TIME>
    <AMOUNT>4102</AMOUNT>
  </ACCEPTED>
  <ACCEPTED>
    <ID>4</ID>
    <TIME>02:31</TIME>
    <AMOUNT>4225</AMOUNT>
  </ACCEPTED>
</ORDERS>