How to create xml string for each row of table?

I have below sql table. I need to add a column xmls to my table such that the table looks like below.

CREATE TABLE employees(
      id          integer,
      name        VARCHAR(MAX),
      designation VARCHAR(MAX),
      manager     integer,
      hired_on    date,
      salary      integer,
      commission  float,
      dept        integer);
INSERT INTO employees VALUES (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4);
INSERT INTO employees VALUES (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3);
INSERT INTO employees VALUES (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3);
INSERT INTO employees VALUES (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3);
SELECT * FROM employees WHERE id < 10;

The new column xmls should look like below(last column below), I know I can do it with the help of string utilities but cannot figure the simple yet effective and optimal solution.

This is what I tried but this does not give me the end tag summary.

SELECT (
SELECT id AS '@id',
designation AS '@type',
salary AS '@salary'
FROM employees b 
WHERE b.id = a.id
FOR XML PATH('summary'))
FROM employees a

Strictly speaking, a self-closing tag ` and a full tag have the same meaning in XML, so it shouldn't make a difference to you, or to any proper XML parser.

But if you want to force it to generate a full tag as opposed to a self-closing one, you can add an empty text() column:
SELECT (
SELECT
e.id AS [@id],
e.designation AS [@type],
e.salary AS [@salary],
'' AS [text()]
FOR XML PATH('summary')
)
FROM employees e;


db<>fiddle

You don't need to self-join in the subquery, you can refer to the outer columns directly. Note also that you should use [] to quote columns where necessary, not ''`.

Side note: you don't need to store this XML, you can create a view that brings it up when you query it.

StackExchange Database Administrators Q#329823, answer score: 10