SQL Server Table data to XML

Question

Hi All,
I need help on generating XML data from sql server table. I am pretty new to XML. Below is my requirement.

Here the similar data sample : (Please check the snapshot of the data attached)

DECLARE @tab TABLE (ID int, col1 nvarchar(100),[file] nvarchar(100),filedate nvarchar(100))
INSERT INTO @tab
VALUES (1,'123','XYZ1.txt,XYZ2.txt','03062021,03072021'),(2,'456','ABC1.txt,ABC2.txt','04062021,04072021')
SELECT * FROM @tab

Below is the XML data i need to generate from above data :

<root>
<MedRecReq>
<ID>1</ID>
<Col1>123</Col1>
<ControlPlanFileNames>
<file>XYZ1.txt</file>
<filedate>03062021</filedate>
</ControlPlanFileNames>
<ControlPlanFileNames>
<file>XYZ2.txt</file>
<filedate>03072021</filedate>
</ControlPlanFileNames>
<ID>2</ID>
<Col1>456</Col1>
<ControlPlanFileNames>
<file>ABC1</file>
<filedate>04062021</filedate>
</ControlPlanFileNames>
<ControlPlanFileNames>
<file>ABC2</file>
<filedate>04072021</filedate>
</ControlPlanFileNames>
</MedRecReq>
</root>

Answer 1

Storing data in a comma-separated list in a column is a very bad idea. Relational databases are not designed for this, and it will only cause you grief. The files should be stored in a sub-table.

Here is a query. You find the function in the query in this article of mine:
http://www.sommarskog.se/arrays-in-sql.html

DECLARE @tab TABLE (ID int, col1 nvarchar(100),[file] nvarchar(100),filedate nvarchar(100))
INSERT INTO @tab
VALUES (1,'123','XYZ1.txt,XYZ2.txt','03062021,03072021'),(2,'456','ABC1.txt,ABC2.txt','04062021,04072021')
SELECT * FROM @tab

SELECT ID, col1 AS Col1, CPFN.doc AS [*]
FROM   @tab
CROSS  APPLY  (SELECT f.nstr AS [File], fd.nstr AS filedate
               FROM dbo.strlist_to_tbl([file], ',') AS f
               JOIN dbo.strlist_to_tbl(filedate, ',') AS fd ON f.listpos = fd.listpos
               FOR XML PATH('ControlPlanFileNames'), TYPE) AS CPFN(doc)
FOR XML PATH ('MedRecReq'), ROOT('root'), TYPE