Hi @Eshwar,
Please try the following solution.
It is using SQL Server XQuery powerful functionality.
USE tempdb;
GO
DROP TABLE IF EXISTS #tempXML;
GO
-- DDL and sample data population, start
CREATE TABLE #tempXML
(
seq_no bigint,
acct_id nvarchar(40),
TableName nvarchar(50),
FieldName nvarchar(50),
FieldType nvarchar(20),
FieldValue nvarchar(500)
);
INSERT INTO #tempXML VALUES
(1,'0393505151','TABLEIM_ACC','fieldlast_deposit_date','date_type','2023-01-12'),
(2,'0393512256','TABLEIM_ACC','fieldavailable_balance','decimal_type','74.61'),
(2,'0393512256','TABLEIM_ACC','fielddda_total','decimal_type','2006169.13'),
(2,'0393512256','TABLEIM_ACC','fieldpast_due_date','date_type','NULL'),
(2,'0393512256','TABLEIM_ACC','fieldlast_deposit_date','date_type','2023-01-08'),
(2,'0393512256','TABLEIM_ACC','fieldcurrent_balance','decimal_type','1999925.39'),
(2,'0393512256','TABLEIM_ACC','fieldinterest_accrued','decimal_type','6243.74');
-- a table to hold raw XML
DECLARE @tbl TABLE (XMLColumn XML);
INSERT @tbl (XMLColumn)
SELECT (SELECT *
FROM #tempXML
--WHERE seq_no <= 3000
ORDER BY seq_no
FOR XML PATH('r'), TYPE, ROOT('root'));
DECLARE @creation_date DATETIMEOFFSET(3) = SYSDATETIMEOFFSET();
SELECT XMLColumn.query('declare default element namespace "http://www.test.com/xml/testdata/data/v1_0";
<dm_data xmlns="http://www.test.com/xml/testdata/data/v1_0">
<header>
<sender_id_txt>source</sender_id_txt>
<target_id_txt>target</target_id_txt>
<batch_id_txt>x</batch_id_txt>
<opd_type_type>CA</opd_type_type>
<total_count>0</total_count>
<creation_data>{sql:variable("@creation_date")}</creation_data>
</header>
<opd>
<act_opd>
{
for $x in distinct-values(/
return <cnsmr_accnt_udp seq_no="{(/
{
if (/
<nullify_fields>
{
for $y in /
return <nullify_field>{data($y/*:FieldName)}</nullify_field>
}
</nullify_fields>
else ()
}
<cnsmr_accnt_idntfr_lgcy_txt>{$x}</cnsmr_accnt_idntfr_lgcy_txt>
<udp_fields>
{
for $y in /
return <udp_field xsi:type="{$y/
<value>{data($y/*:FieldValue)}</value>
</udp_field>
}
</udp_fields>
</cnsmr_accnt_udp>
}
</act_opd>
</opd>
</dm_data>')
FROM @tbl;