FOR XML SQL Server - variable name in XML output
I am new to FOR XML
SQL Server, I have searched significant and I cannot find an answer to this question.
Can I have a variable element name using "for xml" where the element name is not hardcoded and is instead taken from the cell on each line? Let's 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
request:
select ID,
TIME as STATUS_TIME,
AMOUNT as CURRENT_AMOUNT
from ORDERS
for xml raw(' **STATUS NAME HERE** '),root('ORDERS'), elements
Desired 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 that I can give attributes to element names, and that I could give an individual ORDER
in ORDERS
and an attribute STATUS
as shown below, but unfortunately this is not what people who will get an 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 would like to be able to do all of this in SQL Server if possible. Thanks a lot if you can help me at all.
source to share
You cannot specify a column value in XML Raw()
. So, you need to select the required column from the select query and cast result into XML , like so:
Scheme
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>
source to share
In SQL Server, the XML schema must be static, so it is not possible to specify a variable element name (be it a document or an attribute).
If the possible parameters for a field are STATUS
limited and stable, you can specify them explicitly, as in the example below:
select (
select t.ID, t.TIME as [STATUS_TIME], t.AMOUNT as [CURRENT_AMOUNT]
from @temp t
where t.STATUS = 'ACCEPTED'
for xml path('ACCEPTED'), type, elements
), (
select t.ID, t.TIME as [STATUS_TIME], t.AMOUNT as [CURRENT_AMOUNT]
from @temp t
where t.STATUS = 'COMPLETE'
for xml path('COMPLETE'), type, elements
)
for xml path('ORDERS'), type;
I think you've already noticed numerous possibilities for how this code can betray you, but frankly, this approach is the only one available that does not include string manipulation (they will be detrimental to performance if the size of the XML output is at least several MB).
As a possible workaround, you can generate this query dynamically, including as many partitions as there are in your value table STATUS
. Very ugly, but it will work.
source to share