Example: Specifying the ELEMENTXSINIL Directive
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>
...