SQL Server Table data to XML

Gopinath Desai 46 Reputation points
2021-03-06T20:12:43.02+00:00

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 attached75040-xml.png)

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>

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.3K Reputation points
    2021-03-06T21:38:42.793+00:00

    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
    

0 additional answers

Sort by: Most helpful