/*
	So far, we are able to format the XML results in the way we wish. 
	We are able to rename the root node, rows and element names. 

	Now let us look at a different case. When SQL Server generates the 
	XML results, it will skip the columns which has NULL values. Let us
	look at an example. 

	The code below, updates a column to NULL. Look at the results. The 
	third row does not have <Qty> element. 
*/

UPDATE OrderDetails SET Qty = NULL WHERE OrderDetailID = 3

SELECT 
	OrderNumber, 
	ItemNumber, 
	Qty 
FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS, ROOT('orderInfo')

/*
OUTPUT:

<orderInfo>
  <itemInfo>
    <OrderNumber>00001</OrderNumber>
    <ItemNumber>A001</ItemNumber>
    <Qty>10</Qty>
  </itemInfo>
  <itemInfo>
    <OrderNumber>00001</OrderNumber>
    <ItemNumber>A002</ItemNumber>
    <Qty>20</Qty>
  </itemInfo>
  <itemInfo>
    <OrderNumber>00001</OrderNumber>
    <ItemNumber>A003</ItemNumber>
  </itemInfo>
</orderInfo>
*/