SQL: For XML Auto, Elements
Author: jason
Date: 2014-01-24 10:31:55
Category: Musing

To avoid attributes we can use the ELEMENTS tag. This produces a more familiar looking XML string to some people.





With our SELECT statement on Lines 13-16 we'll put in ELEMENTS after AUTO.





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 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 * 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 AUTO, ELEMENTS, ROOT('Fruits')





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