Today’s little task was to grab some data from a database and create an XML file from it. So I decided to play around with the XML features in SQL Server (I used 2008 for this).
One for all and all “FOR XML”
The Microsoft documentation.aspx) told me that the “FOR XML” clause was what I’d need for the task. It comes in four different flavours, each with its own unique talents:
While I won’t delve into each other them, I’m sure the documentation would better explain, I’ll just focus on the ones I found useful. The first was
Following the PATH
Being described as “a simpler way to mix elements and attributes”, it seemed like a good place to start as any. Tacking it onto the end of a query produce a simple XML structure, a
row element per row returned each with child elements per column.
SELECT id, name FROM users FOR XML PATH
Giving you the XML of…
<row> <id>1</id> <name>Bob</name> </row> <row> <id>2</id> <name>Jonesy</name> </row>
Wrapping and attributes
That worked out quite well but it’d be nice to have the “id” element as an attribute on the
row tag. I’d also like to wrap a root element around everything.
SELECT id AS '@id', name FROM users FOR XML PATH, ROOT('Rows')
<Rows> <row id="1"> <name>Bob</name> </row> <row id="2"> <name>Jonesy</name> </row> </Rows>
When is a row not a row?
What you’d like it to be named something else. You may have a specific XML structure that you have to comply with, so having each item wrapped in a
row tag may not be ideal. Thankfully it’s simple to change by just adding the element name to the
PATH as a parameter.
SELECT id AS '@id', name FROM users FOR XML PATH('User'), ROOT('Group')
<Group> <User id="1"> <name>Bob</name> </User> <User id="2"> <name>Jonesy</name> </User> </Group>
Getting to the root of the problem
(“Yes, I can’t help myself with silly title names… sorry”) We’ve now got a root element for the group of users, but what group is it? It’d be useful to add an XML attribute to the
Group tag but from what I could tell, that wasn’t possible via the
ROOT option. So how about using a sub-query?
SELECT 1 AS '@id', (SELECT id AS '@id', name FROM users FOR XML PATH('User') ) FOR XML PATH('Group')
<Group id="1">&lt;User id="1"&gt;&lt;name&gt;Bob&lt;...
Wait… what? Well it turns out that when running my first examples in SQL Management Studio Express that it’d happily treat it as an XML datatype. But when using the returned data for anything else, it ends up as a varchar or similar string type. To work around this, I needed to use an additional clause of TYPE on the end of the FOR XML bit.
The following shows an example of creating an XML document. One of the main differences is my use of FOR RAW on the outer query. This allows me to easily set the name space attributes for the root element.
SELECT -- Root element attributes 'http://www.w3.org/2001/XMLSchema-instance' AS 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema' AS 'xmlns:xsd', ( SELECT -- Creating a default element ( SELECT 0 AS ID, '' AS Name FOR XML PATH('Item'), ELEMENTS, TYPE ), ( SELECT id AS ID, title AS Name FROM items ORDER BY id FOR XML PATH('Item'), ELEMENTS, TYPE ) FOR XML PATH('Items'), TYPE ) FOR XML RAW('Information')
Below is what’s returned
<Information xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Items> <Item> <ID>0</ID> <Name /> </Item> <Item> <ID>1</ID> <Name>Item number A</Name> </Item> <Item> <ID>2</ID> <Name>Item number B</Name> </Item> <Item> <ID>3</ID> <Name>Item number C</Name> </Item> <Item> <ID>4</ID> <Name>Item number D</Name> </Item> <Item> <ID>5</ID> <Name>Item number E</Name> </Item> </Items> </Information>