FOR XML SQL Server - Variable Element name in output XML

Question

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

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

Table 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 query:

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

Required output:

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

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

Output:

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

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.

Answer 1

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 -

Schema

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)

Query

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

Output

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