how to distinct parent tag in for xml path in sql server

Question

CREATE TABLE temp_xmltable1(
Report_id INT,
Report_name VARCHAR(6),
Report_Gender VARCHAR(1),
Report_DOB DATETIME,
Transaction_number INT,
Transaciont_code VARCHAR(2),
Amount INT,
Currency` VARCHAR(3)
);
INSERT INTO temp_xmltable1 VALUES
(1,'Karthi','M','2021-09-03 00:00:00',1001,'AC',2000,'IND'),
(1,'Karthi','M','2021-09-03 00:00:00',1002,'IB',4000,'USD'),
(1,'Karthi','M','2021-09-03 00:00:00',1003,'IB',6000,'GBP');

I need output like below xml format

<report>
<Report_id>1</Report_id>
<reporting_person>
<Report_name>Karthi</Report_name>
<Report_Gender>M</Report_Gender>
<Report_DOB>09-03-21</Report_DOB>
</reporting_person>
<transaction>
<Transaction_number>1001</Transaction_number>
<Transaciont_code>AC</Transaciont_code>
<Amount>2000</Amount>
<Currency>IND</Currency>
</transaction>
<transaction>
<Transaction_number>1002</Transaction_number>
<Transaciont_code>IB</Transaciont_code>
<Amount>4000</Amount>
<Currency>USD</Currency>
</transaction>
<transaction>
<Transaction_number>1003</Transaction_number>
<Transaciont_code>IB</Transaciont_code>
<Amount>6000</Amount>
<Currency>GBP</Currency>
</transaction>
</report>

Answer 1

Hi @kartheeswaran jayakumar ,

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (  
	Report_id INT,  
	Report_name VARCHAR(6),  
	Report_Gender VARCHAR(1),  
	Report_DOB DATETIME,  
	Transaction_number INT,  
	Transaciont_code VARCHAR(2),  
	Amount INT,  
	Currency VARCHAR(3)  
);  
INSERT INTO @tbl VALUES  
(1,'Karthi','M','2021-09-03 00:00:00',1001,'AC',2000,'IND'),  
(1,'Karthi','M','2021-09-03 00:00:00',1002,'IB',4000,'USD'),  
(1,'Karthi','M','2021-09-03 00:00:00',1003,'IB',6000,'GBP');  
-- DDL and sample data population, end  
  
SELECT Report_id  
	, Report_name AS [reporting_person/Report_name]  
	, Report_Gender AS [reporting_person/Report_Gender]  
	, TRY_CAST(Report_DOB AS DATE) AS [reporting_person/Report_DOB]  
	, (SELECT Transaction_number  
		, Transaciont_code  
		, Amount  
		, Currency  
	FROM @tbl  
	FOR XML PATH('transaction'), TYPE)  
FROM @tbl  
GROUP BY Report_id, Report_name, Report_Gender, Report_DOB  
FOR XML PATH('report'), TYPE;  

Output

<report>  
  <Report_id>1</Report_id>  
  <reporting_person>  
    <Report_name>Karthi</Report_name>  
    <Report_Gender>M</Report_Gender>  
    <Report_DOB>2021-09-03</Report_DOB>  
  </reporting_person>  
  <transaction>  
    <Transaction_number>1001</Transaction_number>  
    <Transaciont_code>AC</Transaciont_code>  
    <Amount>2000</Amount>  
    <Currency>IND</Currency>  
  </transaction>  
  <transaction>  
    <Transaction_number>1002</Transaction_number>  
    <Transaciont_code>IB</Transaciont_code>  
    <Amount>4000</Amount>  
    <Currency>USD</Currency>  
  </transaction>  
  <transaction>  
    <Transaction_number>1003</Transaction_number>  
    <Transaciont_code>IB</Transaciont_code>  
    <Amount>6000</Amount>  
    <Currency>GBP</Currency>  
  </transaction>  
</report>