SQL: XML, Adjusting for Node Order
Author: jason
Date: 2014-01-24 10:49:22
Category: Musing

By changing the SELECT statement, in regards to the table order, we can adjust how the XML output will look.

In this XML output I want to list the Categories first, then the Fruit, then the Color. Note there is an "empty" category around Peach. I used RIGHT JOIN to bring in all rows of the Fruit table, regardless if they had an entry in Category. This is just an example. If you have an XML Parser looking for Attributes on the Category Node you might not want to do that...




We can change the Node order by placing the Category table first in the SELECT statement. Then we use RIGHT JOIN to bring the other tables.




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 * FROM @TempCategory Category
RIGHT JOIN @TempFruit Fruit ON Category.categoryID = Fruit.fk_categoryID
RIGHT JOIN @TempColor Color ON Color.colorID = Fruit.fk_colorID
FOR XML AUTO, ROOT('Categories')





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