SQL: Arbitrary XML Attributes
Author: jason
Date: 2014-01-24 11:02:01
Category: Musing


SQL allows us to Alias columns in the SELECT statement if we need to put arbitrary fields into XML Attributes and only want to list certain fields.





In the SELECT statement we can see each column gets an alias like Field1 AS '@Field1'. Using the AT symbol will make an Attribute instead of an Element. I use ISNULL to put in a blank string for the Category, otherwise the word Category wouldn't be displayed at all.





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, 2
INSERT INTO @TempFruit SELECT 3, 'Peach', 3, 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'

SELECT ISNULL(category.category, '') AS '@Category'
, fruit.name AS '@Fruit'
, fruit.number AS '@Number'
, color.color AS '@Color' FROM @TempFruit Fruit
LEFT JOIN @TempColor Color ON Color.colorID = Fruit.fk_colorID
LEFT JOIN @TempCategory Category ON Category.categoryID = Fruit.fk_categoryID
FOR XML PATH('Fruit'), ROOT('Categories')




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