SQL: For XML RAW
Author: jason
Date: 2014-01-24 09:24:21
Category: Musing

Here is one way to get data out of SQL in an XML format. While I do use MSDN and TechNet to figure out the right syntax for the XML statements, I tend to just "try" it in SQL until it looks the way I want. http://technet.microsoft.com/en-us/library/ms178107.aspx

First we'll build some Temp Tables to store our data.




Here we are using FOR XML RAW. I'm adding a ROOT to the XML as well. I'm using LEFT JOINS to make sure the whole Fruit table is brought into the results.




The resulting XML looks like this.




All together now, after you run the query the XML appears in the results window as a clickable entry.




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 @TempFruit Fruit
LEFT JOIN @TempColor Color ON Color.colorID = Fruit.fk_colorID
LEFT JOIN @TempCategory Category ON Category.categoryID = Fruit.fk_categoryID
FOR XML RAW, ROOT('Listing')




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