FOR XML SQL Server - Variable Element name in outp

问题:

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.

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

回答2:

In SQL Server, XML schema has to be static, so it is impossible to specify a variable element name (be it document or attribute).

If possible options for the STATUS field are limited and stable, you can mention them all explicitly, like 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 have already noticed numerous possibilities for how this code can betray you, but frankly this approach is the only one available which does not include string manipulations (they will be detrimental to performance if the size of the XML output will be at least several Mb).

As a possible workaround, you can generate this query dynamically, including as many sections as there are distinct STATUS values in your table. Very ugly, but it will work.