SQL multiple tables into single xml string

Question

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..

<HTable>
	<HID>H1001</HID>
	<HDate>22/08/2023</HDate>
</Header>  
<CTable>
	<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>
</CTable>

<ETable> tag in the xml data also in ##CTable

can anyone pls help this to acheive?

Answer 1

Hi @kasim mohamed,

Please try the following solution.

DECLARE @HTable  table (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');

DECLARE @DTable table  (
	CID nvarchar(50),	-- FOREIGN KEY REFERENCES @HTable(CID)
	DID nvarchar(50) PRIMARY KEY, 
	DType nvarchar(50));
insert into @DTable values ('C1001', 'D1001', 'DType1');
insert into @DTable values ('C1001', 'D1002', 'DType2');

DECLARE @ATable TABLE  (
	CID nvarchar(50),	-- FOREIGN KEY REFERENCES @HTable(CID)
	AID nvarchar(50) PRIMARY KEY, 
	ACode nvarchar(50)
);
insert into @ATable values ('C1001','A1001','ACode1');
insert into @ATable values ('C1001','A1002','ACode2');

DECLARE @OTable table (
	CID nvarchar(50), 	-- FOREIGN KEY REFERENCES @ATable(CID, AID)
	AID nvarchar(50), 	-- FOREIGN KEY REFERENCES @ATable(CID, AID)
	OID nvarchar(50) PRIMARY KEY, 
	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;

SELECT (
SELECT p.*, (
	SELECT c.*
	FROM @DTable AS c
	WHERE c.CID = p.CID
	FOR XML PATH('DTable'), TYPE,ROOT('DTables')
)
, (
	SELECT c.*, (
		SELECT gc.*
		FROM @OTable AS gc
		WHERE c.CID = gc.CID AND c.AID = gc.AID
		FOR XML PATH('OTable'), TYPE,ROOT('OTables')
	)
	FROM @ATable AS c
	WHERE c.CID = p.CID
	FOR XML PATH('ATable'), TYPE,ROOT('ATables')
)
FROM @HTable AS p
FOR XML PATH('HTable'), TYPE, ROOT('HTables')
).query('<Invoice>
{
	for $p in /HTables/HTable
	return (<HTable>
			{$p/HID}{$p/HDate}
		</HTable>,
		<CTable>{$p/CID}{$p/CAmt}
			<ETable>
				{$p/EID}{$p/EType}
			</ETable>
			{
				for $d in $p/DTables/DTable
				return <DTable>
						{$d/DID}{$d/DType}
					</DTable>
			}
			{
				for $a in $p/ATables/ATable
				return <ATable>
						{$a/AID}{$a/ACode}
						{
							for $o in $a/OTables/OTable
							return <OTable>
								{$o/OID}{$o/OType}
							</OTable>
						}
					</ATable>
			}
	</CTable>)
}
</Invoice>');