SQL multiple tables into single xml string

Transact-SQL
1

I have tables like below

create table ##HTable (HID nvarchar(50), HDate DateTime, CID nvarchar(50), CAmt decimal(18,2), EID nvarchar(50), EType nvarchar(50))

insert into ##HTable values ('H1001','2023-08-22','C1001', 200, 'E1001', 'EType1')

create table ##DTable (CID nvarchar(50), DID nvarchar(50), DType nvarchar(50))

insert into ##DTable values ('C1001', 'D1001', 'DType1');

insert into ##DTable values ('C1001', 'D1002', 'DType2');

create table ##ATable (CID nvarchar(50), AID nvarchar(50), ACode nvarchar(50))

insert into ##ATable values ('C1001','A1001','ACode1');

insert into ##ATable values ('C1001','A1002','ACode2');

create table ##OTable (CID nvarchar(50), AID nvarchar(50), OID nvarchar(50), OType nvarchar(50))

insert into ##OTable values ('C1001','A1001','O1001','OType1');

insert into ##OTable values ('C1001','A1001','O1002','OType2');

select * from ##HTable

select * from ##DTable

select * from ##ATable

select * from ##OTable

drop table ##HTable

drop table ##DTable

drop table ##ATable

drop table ##OTable

the ##HTable in primary table and ##DTable and ##ATable is subtable of ##HTable

and ##OTable is subtable of ##Atable.

i want to generate xml string like below from the above table..

<HID>H1001</HID>

<HDate>22/08/2023</HDate>

<CID>C1001</CID>

<CAmt>200.00</CAmt>

<ETable>

	<EID>E1001</EID>

	<EType>EType1</EType>

</ETable>

<DTable>

	<DID>D1001</DID>

	<DType>DType1</DType>

</DTable>

<DTable>

	<DID>D1002</DID>

	<DType>DType2</DType>

</DTable>

<ATable>

	<AID>A1001</AID>

	<ACode>ACode1</ACode>

	<OTable>

		<OID>O1001</OID>

		<OType>OType1</OType>

	</OTable>

	<OTable>

		<OID>O1002</OID>

		<OType>OType2</OType>

	</OTable>

</ATable>

<ATable>

	<AID>A1002</AID>

	<ACode>ACode2</ACode>

</ATable>

ETable tag in the xml data also in ##CTable

can anyone pls help this to acheive?

2

Shape XML with Nested FOR XML Queries - SQL Server | Microsoft Learn