example-specifying-the-elementxsinil-directive.md

title	Example: Specifying the ELEMENTXSINIL Directive | Microsoft Docs
ms.custom
ms.date	03/01/2017
ms.prod	sql
ms.prod_service	database-engine
ms.reviewer
ms.technology	xml
ms.topic	conceptual
helpviewer_keywords	ELEMENTXSINIL directive
ms.assetid	bbcb6f9e-a51b-4775-9795-947c9d6d758f
author	MightyPen
ms.author	genemi

Example: Specifying the ELEMENTXSINIL Directive

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md] When you specify the ELEMENT directive to retrieve element-centric XML, if the column has a NULL value, the corresponding element is not generated by the EXPLICIT mode. You can optionally specify the ELEMENTXSINIL directive to request the generating element for NULL values where the xsi:nil attribute is set with the value TRUE.

The following query constructs XML that includes an employee address. For AddressLine2 and City columns, the column names specify the ELEMENTXSINIL directive. This generates the element for NULL values in the AddressLine2 and City columns in the rowset.

USE AdventureWorks2012;  
GO  
SELECT 1    as Tag,  
       NULL as Parent,  
       E.BusinessEntityID  as [Employee!1!EmpID],  
       BEA.AddressID as [Employee!1!AddressID],  
       NULL        as [Address!2!AddressID],  
       NULL        as [Address!2!AddressLine1!ELEMENT],  
       NULL        as [Address!2!AddressLine2!ELEMENTXSINIL],  
       NULL        as [Address!2!City!ELEMENTXSINIL]  
FROM   HumanResources.Employee AS E  
INNER JOIN Person.BusinessEntityAddress AS BEA  
    ON E.BusinessEntityID = BEA.BusinessEntityID  
  
UNION ALL  
SELECT 2 as Tag,  
       1 as Parent,  
       E.BusinessEntityID,  
       BEA.AddressID,  
       A.AddressID,  
       AddressLine1,   
       AddressLine2,  
       City   
FROM   HumanResources.Employee AS E  
INNER JOIN Person.BusinessEntityAddress AS BEA  
    ON E.BusinessEntityID = BEA.BusinessEntityID  
INNER JOIN Person.Address AS A  
    ON BEA.AddressID = A.AddressID  
ORDER BY [Employee!1!EmpID],[Address!2!AddressID]  
FOR XML EXPLICIT;  

This is the partial result:

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          EmpID="1"
          AddressID="249">
  <Address AddressID="249">
    <AddressLine1>4350 Minute Dr.</AddressLine1>
    <AddressLine2 xsi:nil="true" />
    <City>Minneapolis</City>
  </Address>
</Employee>
...

See Also

Use EXPLICIT Mode with FOR XML