Columns that Contain a Null Value By Default

Columns that Contain a Null Value B...

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

SQL Server 2008 Books Online (June 2009)

Columns that Contain a Null Value By Default

By default, a null value in a column maps to the absence of the attribute, node, or element. This default behavior can be overwritten by requesting element-centric XML using the ELEMENTS directive and specifying XSINIL to request adding elements for NULL values, as shown in the following query:

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

The following shows the result. Note that if XSINIL is not specified, the <Middle> element will be absent.

<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>

Concepts

Using PATH Mode

Help and Information

Getting SQL Server 2008 Assistance

Tags

: Add a tag

Community Content

Add new content

Annotations

Adding empty tags on Null return from table

Eric Stott | Edit | Show History

An easy way to create the tags without having the extra namespace and xsi:nil="true" in the tag is to simply use the ISNULL function:

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



returuns the folowing xml

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