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>