Generate XML using SQL script

Question

HI,

I couldn't find a forum for transact-sql questions so posting here.

I am trying to generate a XML output using t-sql.

Below is my input:

Data Query:

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
SELECT 1,'0393505151','TABLEIM_ACC','fieldlast_deposit_date','date_type','2023-01-12' UNION ALL
SELECT 2,'0393512256','TABLEIM_ACC','fieldavailable_balance','decimal_type','74.61' UNION ALL
SELECT 2,'0393512256','TABLEIM_ACC','fielddda_total','decimal_type','2006169.13' UNION ALL
SELECT 2,'0393512256','TABLEIM_ACC','fieldpast_due_date','date_type','NULL' UNION ALL
SELECT 2,'0393512256','TABLEIM_ACC','fieldlast_deposit_date','date_type','2023-01-08' UNION ALL
SELECT 2,'0393512256','TABLEIM_ACC','fieldcurrent_balance','decimal_type','1999925.39' UNION ALL
SELECT 2,'0393512256','TABLEIM_ACC','fieldinterest_accrued','decimal_type','6243.74'

SELECT *FROM #tempXML

Expected output:

Samplexml.xml

Few more inputs:

• A Static header lines 1-12
• A Static footer lines 44-46
• If there is a NULL value field in the input for that acct_id then it should come before (with tag <nullify_fields><nullify_field>fieldname1></nullify_field><nullify_field>fieldname2></nullify_field></nullify_fields>)any other node data

Appreciate your inputs!

Thanks,

Eshwar.

Answer 1

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;