question

GopinathDesai-8233 avatar image
0 Votes"
GopinathDesai-8233 asked ·

SQL Server Table data to XML

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>


sql-server-transact-sql
xml.png (68.3 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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
· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for quick response @ErlandSommarskog
But is see <doc> </doc> node ,how can we avoid these nodes

0 Votes 0 ·

Sorry, I didn't notice those nodes when I've tested. I have corrected the script.

0 Votes 0 ·

hey @ErlandSommarskog , you are just awesome. Script worked like a charm. I have become a fan of yours. Thank you so much for such a proactive responses and helping me out.

Take a Bow!!!!

0 Votes 0 ·