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