Tuesday 15 January 2013

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

No comments: