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.
SELECT 1 as Tag, NULL as Parent, EmployeeID as [Employee!1!EmpID], E.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.EmployeeAddress E, Person.Address A WHERE E.ContactID = A.ContactID UNION ALL SELECT 2 as Tag, 1 as Parent, EmployeeID, E.AddressID, A.AddressID, AddressLine1, AddressLine2, City FROM HumanResources.EmployeeAddress E, Person.Address A WHERE E.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="61"> <Address AddressID="61"> <AddressLine1>7726 Driftwood Drive</AddressLine1> <AddressLine2 xsi:nil="true" /> <City>Monroe</City> </Address> </Employee> ...