Generate XML using SQL script

Eshwar 196 Reputation points
2023-01-23T16:39:55.82+00:00

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:

User's image

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,263 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,561 Reputation points
    2023-01-23T19:29:46.3333333+00:00

    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;
    
    1 person found this answer helpful.