SQL: XML Clever Combining
Author: jason
Date: 2014-01-24 11:59:29
Category: Musing

What if you want to combine two distinctly different tables into 1 gigantic XML string? You can! Cast each SELECT statement to a VARCHAR then mash them together and CAST it all back to XML.





With our tables we've got Fruit, which is linked to Color and Category. And we've got Employee, which isn't linked to anything.




Here is the big SELECT statement.





Code

DECLARE @TempFruit AS TABLE ([fruitID] BIGINT, [name] VARCHAR(10), [number] INT, [fk_colorID] BIGINT, [fk_categoryID] BIGINT)
INSERT INTO @TempFruit SELECT 1, 'Apple', 10, 1, 1
INSERT INTO @TempFruit SELECT 2, 'Tangerine', 5, 2, NULL

DECLARE @TempColor AS TABLE ([colorID] BIGINT, [color] VARCHAR(10))
INSERT INTO @TempColor SELECT 1, 'Red'
INSERT INTO @TempColor SELECT 2, 'Orange'

DECLARE @TempCategory AS TABLE ([categoryID] BIGINT, [category] VARCHAR(10))
INSERT INTO @TempCategory SELECT 1, 'Yummy'
INSERT INTO @TempCategory SELECT 2, 'Other'

DECLARE @TempEmployee AS TABLE ([employeeID] BIGINT, [FirstName] VARCHAR(10))
INSERT INTO @TempEmployee SELECT 1, 'Jason'
INSERT INTO @TempEmployee SELECT 2, 'John'

SELECT CAST(
CAST(
(SELECT fruit.name AS '@Name'
, fruit.number AS '@Number'
, (SELECT color FROM @TempColor color
WHERE color.colorID = fruit.fk_colorID) AS '@Color'
, ISNULL((SELECT category FROM @TempCategory category
WHERE category.categoryID = fruit.fk_categoryID), '') AS '@Category'
FROM @TempFruit Fruit
FOR XML PATH('Fruit'), ROOT('Fruits')
) AS VARCHAR(MAX))
+ CAST(
(SELECT FirstName AS '@FirstName'
FROM @TempEmployee
FOR XML PATH('Employee'), ROOT('Employees')
) AS VARCHAR(MAX))
AS XML) FOR XML PATH('Listing')




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009