question

kartheeswaranjayakumar-5174 avatar image
0 Votes"
kartheeswaranjayakumar-5174 asked YitzhakKhabinsky-0887 answered

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

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>

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered

Hi @kartheeswaranjayakumar-5174,

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>
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.