By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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')
2 rows affected
SELECT (
SELECT *
FROM testTable DATA
FOR XML AUTO, ROOT ('Events'), TYPE
)
FOR XML PATH('EventList'), TYPE;
| (No column name) |
|---|
| <EventList><Events><DATA date="1901-01-01T00:00:00" name="a" /><DATA date="1902-02-02T00:00:00" name="b" /></Events></EventList> |
SELECT (
SELECT
date AS [@date],
name AS [@name]
FROM testTable
FOR XML PATH('DATA'), ROOT('Events'), TYPE
)
FOR XML PATH('EventList'), TYPE;
| (No column name) |
|---|
| <EventList><Events><DATA date="1901-01-01T00:00:00" name="a" /><DATA date="1902-02-02T00:00:00" name="b" /></Events></EventList> |