sharon.chapman7 (11/19/2012)
Hi,Can you tell me how to write this code if I only want certain fields from each table? I get errors when I try to select a few fields to insert into the temp table.
Thanks,
Sharon
PS thanks for posting this code, it was really helpful and I didn't see where anyone else posted code to combine two unrelated tables into one xml file.
Hi Sharon,
I would simply list the columns in each of the SELECT statements.
For instance (taking my example):
DECLARE @TempExportTable TABLE
(
Products XML,
Colours XML,
Sizes XML
)
INSERT INTO @TempExportTable VALUES
(
(SELECT ProductID, PR_Description FROM Products FOR XML AUTO, ELEMENTS),
(SELECT ColourID, CO_Description FROM Colours FOR XML AUTO, ELEMENTS),
(SELECT SizeID, SI_Description FROM Sizes FOR XML AUTO, ELEMENTS)
)
SELECT
Products as '*',
Colours as '*',
Sizes as '*'
from @TempExportTable
FOR XML PATH('ExportList')
I would, of course use the column names you assigned to your tables as they would not be the same 😀
Kind Regards,
Riaan