Empty tags formatting in XML column in SQL Server

Question

I have xml like this:

<root>
   <name></name>
</root>

When I save it do database to XML column and query e.g in SSMS, it is formatted with self closing tags:

<root>
   <name />
</root>

Is it possible to keep original formatting, or determine the formatting in SELECT statement?

Answer 1

If you are getting the data in a non-xml format, and don't want a Self closing tag, you'll need to replace the NULL with an empty string: ISNULL([YourColumn],'').

For example:

CREATE TABLE #Sample ([name] char(1));
INSERT INTO #Sample
VALUES(NULL);


SELECT ISNULL([Name],'') AS [name]
FROM #Sample
FOR XML PATH('root');

DROP TABLE #Sample;

If, however, you're inserting that xml into SQL Server, as an xml type, and then returning it, then SQL Server will use self-closing tags (as per my comment on the question).

As @DavidG said, any good xml parser will be able to read both self closing and non-self closing tags. If your parser can't read self closing tags, you need to consider updating your parser. If it's purely for display purposes... Well why are you using the "old" way of doing it for display?