When you write a simple FOR XML query with PATH, you will see that a <row> element will be generated for each row in the result set. For example:
DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
SELECT Name FROM @t
FOR XML PATH, ROOT ('Employees')
/*
<Employees>
<row>
<Name>Jacob</Name>
</row>
<row>
<Name>Steve</Name>
</row>
</Employees>
*/
Note that a <node> element is created for each row in the query result. PATH is a very powerful operator that allows a great deal of flexibility. Most of the operations previously possible only with EXPLICIT is now possible with PATH. Let us see a few simple variations of the above query and see how we could control the format of the output.
Let us first of all, remove the <node> element.
DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
SELECT Name FROM @t
FOR XML PATH(''), ROOT ('Employees')
/*
<Employees>
<Name>Jacob</Name>
<Name>Steve</Name>
</Employees>
*/
Now, Let us put each employee under an <Employee> Node.
DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
SELECT Name FROM @t
FOR XML PATH('Employee'), ROOT ('Employees')
/*
<Employees>
<Employee>
<Name>Jacob</Name>
</Employee>
<Employee>
<Name>Steve</Name>
</Employee>
</Employees>
*/
Finally, lets us change the <Name> element to an attribute.
DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
SELECT Name AS '@Name' FROM @t
FOR XML PATH('Employee'), ROOT ('Employees')
/*
<Employees>
<Employee Name="Jacob" />
<Employee Name="Steve" />
</Employees>
*/
Is there a FOR XML query that you find hard to write? Send me a note and I will try to help you out.
FOR XML Tutorials