combine/group xml element attributes in one line in SQL

Question

I would like to know how to combine/group elements in one line named "DATA" in SQL to XML. It is similar, but not identical to: combine/group xml element attributes in one line

I have created an example Table and my try in SQLFiddle: http://sqlfiddle.com/#!18/8819e/1 using MS Sequal Server 2017

To make my problem more clear, below is the SQL code, current output and desired output.

SQL Example data

CREATE TABLE testTable(
  date datetime,
  name varchar(50)
  );
  
 INSERT INTO testTable (date, name)
 VALUES ('1901-01-01 00:00:00', 'a'), ('1902-02-02 00:00:00', 'b')

SQL Query

select * from testTable FOR XML path('EventList'), ROOT ('Events')

Current output

<Events>
  <EventList>
    <date>1901-01-01 00:00:00</date>
    <name>a</name>
  </EventList>
  <EventList>
    <date>1902-02-02 00:00:00</date>
    <name>b</name>
  </EventList>
  <EventList>
<Events>

Desired output

<Events>
  <EventList>
    <DATA date="1901-01-01 00:00:00" name="a" />
    <DATA date="1902-02-02 00:00:00" name="b" />
  </EventList>
</Events>  

Answer 1

If you use FOR XML AUTO then the table name becomes the name of each row node.

The outer node can be specified using ROOT but you can only do one level so you need to nest it

SELECT (
    SELECT *
    FROM testTable DATA
    FOR XML AUTO, ROOT ('Events'), TYPE
)
FOR XML PATH('EventList'), TYPE;

Alternatively you can do FOR XML PATH for the subquery also

SELECT (
    SELECT
      date AS [@date],
      name AS [@name]
    FROM testTable
    FOR XML PATH('DATA'), ROOT('Events'), TYPE
)
FOR XML PATH('EventList'), TYPE;

db<>fiddle