FOR XML SQL Server-输出XML中的变量元素名称 [英] FOR XML SQL Server - Variable Element name in output XML

问题描述

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

I'm quite new to FOR XML in SQL Server, I've searched considerable and I can't find an answer to this.

我可以使用'for xml'使用可变元素名称吗，其中元素名称不是硬编码的，而是从每一行的单元格中提取的?以下面的示例为例...

Can I have a variable element name using 'for xml' where the element name is not hard-coded and is instead take from a cell in each row? Take the following example...

表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>

我知道我能够为元素名称赋予属性，并且我可以给ORDERS中的单个ORDER和STATUS的属性，如下所示，但不幸的是，这不是将要收到的人的名字. XML文档正在寻找:(

I know I'm able to give attributes to the element names, and that I could give the individual ORDER in ORDERS and attribute of STATUS like below but unfortunately that's not what the people that will receive the XML document are looking for :(

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中完成所有这些操作. 非常感谢您能为此提供帮助.

I'd like to be able to do all this within SQL Server if possible. Many, many thanks if you can help me at all on this.

推荐答案

您不能在XML Raw()中指定列值.因此，您需要做的是从选择查询中选择必填列，然后将结果转换为 XML ，如下所示-

You can't specify column value in XML Raw(). So what you have to do is select required column from select query and cast result into XML, like this -

架构

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>

这篇关于FOR XML SQL Server-输出XML中的变量元素名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！