Generating XML From SQL Server Tables

SQL Server has the capability to generate XML documents from its tables.  This is done by using the FOR XML clause along with the SELECT statement.  The FOR XML clause offers 4 different modes:

• RAW
• AUTO
• PATH
• EXPLICIT

The following video contains a tutorial on how to use FOR XML with the PATH option, which is the most widely used option.

Below are SQL statements used in the video:

---

SELECT *
FROM Production.Product
FOR XML AUTO

SELECT
ProductID,
Name,
ProductNumber,
ListPrice,
ModifiedDate
FROM Production.Product
FOR XML PATH('Product'), ROOT('Products')

SELECT
ProductID AS [@ProductID],
Name AS [ProductInfo/@Name],
ProductNumber AS [ProductInfo/ProductNumber],
ListPrice AS [ProductInfo/ListPrice],
ModifiedDate AS [ModifiedDate]
FROM Production.Product
FOR XML PATH('Product'), ROOT('Products')

SELECT * FROM Production.Product
SELECT * FROM Production.ProductSubcategory

SELECT
PSC.ProductSubcategoryID AS [@ProductSubcategoryID],
PSC.Name AS [@Name],
(SELECT
ProductID,
Name,
ProductNumber,
ListPrice,
ModifiedDate
FROM Production.Product P
WHERE P.ProductSubcategoryID = PSC.ProductSubcategoryID
FOR XML PATH('Product'), ROOT('Products'), TYPE)
FROM Production.ProductSubcategory PSC
FOR XML PATH ('Subcategory'), ROOT ('Subcategories')

DECLARE @x xml
SET @x =
(SELECT
ProductID AS [@ProductID],
Name AS [ProductInfo/@Name],
ProductNumber AS [ProductInfo/ProductNumber],
ListPrice AS [ProductInfo/ListPrice],
ModifiedDate AS [ModifiedDate]
FROM Production.Product
FOR XML PATH('Product'), ROOT('Products'), TYPE)

SELECT @x

---