SQL Server : FOR XML Basics

FOR XML clause supports 4 modes

• RAW 
• AUTO 
• PATH 
• EXPLICIT 

RAW produces 1 line per record.

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML RAW;

AUTO on it's own lists columns as attributes

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML AUTO;

AUTO, ELEMENTS makes each column an element.

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML AUTO, ELEMENTS;
 

 

  1
  Ken
  J
  Sánchez


  2
  Terri
  Lee
  Duffy


  3
  Roberto
  Tamburello

XML PATH

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML PATH('Donkey');

 

  1
  Ken
  J
  Sánchez


  2
  Terri
  Lee
  Duffy


  3
  Roberto
  Tamburello

For XML EXPLICIT to work, the first 2 columns must be Tag and Parent.
Need to UNION ALL a header row to the data.

 
SELECT    1 AS Tag
         ,NULL AS Parent
         ,ProductCategoryID as [Category!1!ID]
         ,Name AS [Category!1!Name]
         ,NULL AS [Subcategory!2!Name] -- Placeholder for Subcategory.Name
FROM Production.ProductCategory 
 
UNION ALL 
 
SELECT   2 AS Tag
        ,1 AS Parent
        ,s.ProductSubcategoryID
        ,c.Name -- (1) Placeholder for Category.Name.  (2) Category.Name is needed for ORDER BY 
                ,s.Name 
FROM Production.ProductSubcategory s,
Production.ProductCategory c
WHERE c.ProductCategoryID = s.ProductCategoryID 
ORDER BY [Category!1!Name], [Subcategory!2!Name] 
FOR XML EXPLICIT 

Links 
Simple Talk : Using the FOR XML Clause