CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
Kartikeya
Kartikeya

Reputation: 53

How to display empty XML tag in MS SQL Server

I have a requirement of exporting my data into a XML format. For simplicity, consider that I have a table Employee with FName,MName and LName columns. To export the data into XML, I have written the following query

SELECT FName, MName, LName from Employee FOR XML PATH ('Emp Details'), ROOT ('Details');

This return the data in correct XML format. However, if there is any row with NULL value, it skips the XML tag for that column. Like in following example MName had a NULL value therefore the tag for MName was missing in the XML as well:-

<Emp Details>
  <Details>
    <FName>Rohit</Fname>
    <LName>Kumar</LName>
  </Details>
</Emp Details>

I have tried using ELEMENTS XSINIL method but it displays the tag like

<MName xsi:nil="true" />

however, I want it to be displayed as

<MName />

Is it possible to display the NULL value XML tags in such a manner? Thanks

Upvotes: 0

Views: 2350

Answers (2)

Thom A
Thom A

Reputation: 95544

As I mention in the comments, wrap the value with ISNULL to return a blank string:

SELECT Fname,
       LName,
       ISNULL(MName,'') AS MName
FROM (VALUES('Rohit','Kumar',NULL))V(FName,LName,MName)
FOR XML PATH('Details'),ROOT('EmpDetails'); --Nodes can't have spaces in their names

This returns the below XML:

<EmpDetails>
    <Details>
        <Fname>Rohit</Fname>
        <LName>Kumar</LName>
        <MName></MName>
    </Details>
</EmpDetails>

Note that SQL Server does not generate blank nodes using the shorter syntax, it will explicitly open and close the tags.

Upvotes: 1

FrankPl
FrankPl

Reputation: 929

If you want to have an empty MName element in the result, just convert the NULL value to an empty string in the select statement:

SELECT  FName, coalesce(MName, '') as MName, LName
from Employee
FOR XML PATH ('Emp Details'), ROOT ('Details');

coalesce is the ANSI standard way of converting NULL values to something, SQL Server accepts this as well as the SQL Server specific IsNull.

Upvotes: 1

Related Questions