Using For XML Clause In SQL Queries

XML

XML acronym for eXtensible Markup Language. This markup language is much similar to HTML. It is designed to store and transport data. Moreover, the XML tag is not predefined but designed to be self-descriptive.

For XML Clause

The XML clause converts the SQL result set into XML format. It is a very much helpful clause when we need XML data from the SQL result set. The FOR XML clause can be used in top-level queries and subqueries. The top-level FOR XML clause can be used only in the SELECT statement. In sub-queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements.

There are four modes of For XML Clause. Like

Raw
Auto
Path
Explicit

Elaboration of Modes

Raw

Each row is turned into an XML node. That is called a row by default, but you change the node name at any time by your name. Every column will convert as an attribute.

select Id, Name, CatId from Product for xml raw

Now replace the default row node name with your custom name.

select Id, Name, CatId from Product for xml raw('product')

It is also possible to set a root element in this XML structure. Here it is the same as before. The default root element name is root. But you can change the root element name.

select Id, Name, CatId from Product for xml raw('product'), root

Moreover, you can also set elements instead of attributes.

select Id, Name, CatId from Product for xml raw('product'), root, elements

Auto

Similar to raw mode, but here uses table name as the default node name instead of row. Here everything is possible as in raw mode. Like the root, elements can be used.

select Id, Name, CatId from Product for xml auto

But you can't set a custom node name instead of the default table name in this mode.

Use of root and elements in auto mode.

select Id, Name, CatId from Product for xml auto, root('products'), elements

Difference between raw and auto mode

When you retrieve data from multiple tables by joining, you will see a big difference between the two modes.

Raw mode query

select Category.Name as Title, Product.Name from Product 
inner join Category on Product.CatId = Category.CatId
for xml raw, root

Auto mode query

select Category.Name as Title, Product.Name from Product 
inner join Category on Product.CatId = Category.CatId
for xml auto, root

Result

Path

This mode will convert each record as a parent element and every column as a nested element. There is no default attribute, but you can set custom attributes. You can also use rows, roots, and elements in this mode. This mode is the best and better.

select Category.Name as Title, Product.Name from Product 
inner join Category on Product.CatId = Category.CatId
for xml path

Use of custom parent path and root element in path mode.

select Category.Name as Title, Product.Name from Product 
inner join Category on Product.CatId = Category.CatId
for xml path('Category'), Root('Products')

You can set attributes in this path mode very quickly. But remember that assigning an attribute must be used @sign with the column name alias.

select Category.Name as [@Title], Product.Name from Product 
inner join Category on Product.CatId = Category.CatId
for xml path('Category'), Root('Products')

Explicit

This mode is used to generate its own custom XML structured format. So, you can choose to generate your XML structure.

<ELEMENT> - The name of the element to which values will be assigned.
<TAG> - The tag number represents the level in hierarchy or depth.
<ATTRIBUTE> - The name of the attribute to which a particular column's value will be assigned.
<DIRECTIVE> - This is optional and used to provide additional information for XML creation. We will look at one of its options, "ELEMENT."

The first two columns are Tag and Parent and are Meta columns. These values determine the hierarchy. Moreover, this two-column name must be Tag and Parent. This name is required.

SELECT        1 AS TAG,
              NULL AS PARENT,
              c.Name AS [Category!1!Name],
			  NULL AS [Sales!2!SaleID],
              p.Name AS [Sales!2!Product!ELEMENT],
              NULL AS    [Sales!2!Quantity!ELEMENT],
              NULL AS [Sales!2!Date!ELEMENT]
FROM          [Product] p 
			  INNER JOIN Category c ON p.CatId = c.CatId
			  WHERE p.Id in (SELECT ProductId FROM Sales)
			  UNION ALL
			  SELECT        2 AS TAG,
              1 AS PARENT,
              c.Name AS [Category!1!Name],
			  s.SaleId AS [Sales!2!SaleID],
              p.Name AS [Sales!2!Product!ELEMENT],
              s.Quantity AS    [Sales!2!Quantity!ELEMENT],
              s.Date AS [Sales!2!Date!ELEMENT]
FROM          [Product] p 
			  INNER JOIN Category c ON p.CatId = c.CatId
			  INNER JOIN Sales s ON s.ProductId = p.Id
			  WHERE p.Id = s.ProductId
			  ORDER BY  [Category!1!Name], [Sales!2!Product!ELEMENT], [Sales!2!SaleID]
			  FOR XML EXPLICIT

Conclusion

Happy coding, and thanks for reading my article!!! I hope this article has helped you to understand the SQL XML clause. Here I have tried to explain very simply all terms of this clause.