How to display empty XML tag in MS SQL Server

Question

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

Use '' instead, that'll force the node to be displayed. So ISNULL(MName,'') would force the node to appear, but blank. — Thom A
– Thom A ♦, Commented Mar 11, 2021 at 10:12

Thom A · Accepted Answer · 2021-03-11 10:17:03Z

1

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.

answered Mar 11, 2021 at 10:17

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kartikeya Over a year ago

Thanks, its working now. But can we restrict the empty tag as <MName/>. Although I believe, XML considers both the same.

Thom A Over a year ago

@Kartikeya I address that in the answer.

FrankPl · Accepted Answer · 2021-03-11 10:14:34Z

1

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.

answered Mar 11, 2021 at 10:14

FrankPl

9395 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to display empty XML tag in MS SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related