Feb 042009
 

In creating xml data from table data, I came across the need to create empty elements even in there are null values in the columns I am querying.

I first used this logic to force the generation of the tag:

SELECT EmployeeID as "@EmpID", FirstName as "EmpName/First", MiddleName as "EmpName/Middle", LastName as "EmpName/Last" FROM HumanResources.Employee E, Person.Contact C WHERE E.EmployeeID = C.ContactID AND E.EmployeeID=1 FOR XML PATH, ELEMENTS XSINIL

However that returned the result:

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" EmpID="1"> <EmpName> <First>Gustavo</First> <Middle xsi:nil="true" /> <Last>Achong</Last> </EmpName> </row>

I just wanted:

<row EmpID="1"> <EmpName> <First>Gustavo</First> <Middle></Middle> <Last>Achong</Last> </EmpName> </row>

So I changed the code to:

SELECT EmployeeID as "@EmpID", ISNULL(FirstName,'') as "EmpName/First", ISNULL(MiddleName,'') as "EmpName/Middle", ISNULL(LastName,'') as "EmpName/Last" FROM HumanResources.Employee E, Person.Contact C WHERE E.EmployeeID = C.ContactID AND E.EmployeeID=1 FOR XML PATH, ELEMENTS