Convert SQL Server data into XML

Question

username	email	firstname	lastname	role	timezone	jobcode
jdoe	jd@gmail.com	John	Doe	acct	est	j001
jdoe	jd@gmail.com	John	Doe	acct	est	j002

Data in SQL Server table above

I need XML output like this from the sql server table above.

<Employees>
<Employee username="jode" email="jd@gmail.com" firstname="John" lastname="Doe">
<Role role="acct'/>
<Timezone timezone="est"/>
<Job job="j001" />
<Job job="j002" />
</Employee>
</Employees>

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky, Mar 17 '23 at 17:53
https://www.sqlservercentral.com/articles/basics-of-xml-and-sql-server-part-2-shredding-xml — granadaCoder, Mar 17 '23 at 17:57
https://stackoverflow.com/questions/61233/the-best-way-to-shred-xml-data-into-sql-server-database-columns — granadaCoder, Mar 17 '23 at 17:57
This type of question has been asked (and answered) dozens of times. Please research. — granadaCoder, Mar 17 '23 at 17:58

score 1 · Accepted Answer · answered Mar 17 '23 at 18:33

Please try the following solution.

As you can see, aliases allow to compose XML with attributes.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, 
    username VARCHAR(20),
    email VARCHAR(128),
    firstname VARCHAR(20),
    lastname VARCHAR(20),
    role VARCHAR(20),
    timezone CHAR(3),
    jobcode VARCHAR(10)
);
INSERT @tbl (username, email, firstname, lastname, role, timezone, jobcode) VALUES
('jdoe', 'jd@gmail.com', 'John', 'Doe', 'acct', 'est', 'j001'),
('jdoe', 'jd@gmail.com', 'John', 'Doe', 'acct', 'est', 'j002');
-- DDL and sample data population, end

SELECT * FROM @tbl;

SELECT username AS [@username]
    , email AS [@email]
    , firstname AS [@firstname]
    , lastname AS [@lastname]
    , role AS [Role/@role]
    , timezone AS [Timezone/@timezone]
    , (
    SELECT jobcode AS [@job]
    FROM @tbl AS c
    WHERE c.email = p.email
    FOR XML PATH('Job'), TYPE
)
FROM @tbl AS p
GROUP BY username, email, firstname, lastname, role, timezone
FOR XML PATH('Employee'), TYPE, ROOT('Employees');

Output

<Employees>
  <Employee username="jdoe" email="jd@gmail.com" firstname="John" lastname="Doe">
    <Role role="acct" />
    <Timezone timezone="est" />
    <Job job="j001" />
    <Job job="j002" />
  </Employee>
</Employees>

Convert SQL Server data into XML

1 Answers1