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 isn’t generated by the EXPLICIT mode. You can

optionally specify the ELEMENTXSINIL directive to request the generating element for NULL

values where the

attribute is set with the value TRUE.

The following query constructs XML that includes an employee address. For

and

columns, the column names specify the

directive. This generates the

element for NULL values in the

and

columns in the rowset.

This is the partial result:

AddressLine2
City
ELEMENTXSINIL
AddressLine2
City
USE
AdventureWorks2022;
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;