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?

Using non-self closing tags in xml is a deprecated feature. SQL Server is using self closing tags, because that's the correct way to do it. — Thom A
– Thom A ♦, Commented Feb 12, 2018 at 10:12
I don't understand why you care, both syntax contain the same information to any XML parser? — DavidG
– DavidG, Commented Feb 12, 2018 at 10:21
@DavidG: well, it's only because I'm migrating data from sharepoint to sql server and I want to be able to compare the original xml with the new one more easily. — Liero
– Liero, Commented Feb 12, 2018 at 10:33
Then I suggest using a proper method to compare XML, for example stackoverflow.com/questions/167946/… — DavidG
– DavidG, Commented Feb 12, 2018 at 10:34
I agree. If there is no way to format the XML in SSMS or in Select Clause, I will accept it as answer. Not sure who deserves the credit more, Larni or DavidG, though. — Liero
– Liero, Commented Feb 12, 2018 at 10:37

Thom A · Accepted Answer · 2018-02-12 10:24:32Z

3

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?

answered Feb 12, 2018 at 10:24

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

This is true for this one single step. But even a correct XML might change within an XML query... (see my answer)

Gottfried Lesigang · Accepted Answer · 2018-02-12 10:49:47Z

The self-closing element <SomeElement/> is - semantically - the same as <SomeElement></SomeElement>. You should not bother about this... If your reading tool (or a third party requirement) needs this, you should rather replace this tool or discuss this with your partner.

The problem is: You have no control, that things stay as they are. Even in cases, where you are able to store the empty value with an opening and a closing tag this might be changed implicitly with a later call.

Try this:

DECLARE @tbl TABLE(ID INT IDENTITY,YourXML XML);
INSERT INTO @tbl VALUES
(
    (SELECT '' AS [SomeTag] FOR XML PATH('RowNode'),ROOT('RootNode'),TYPE)
)
,(
    (SELECT '' AS [SomeTag] FOR XML RAW('RowNode'),ROOT('RootNode'), ELEMENTS)
)
,(
    N'<RootNode>
      <RowNode>
        <SomeTag></SomeTag>
      </RowNode>
    </RootNode>'
)
,
(CAST(
    N'<RootNode>
      <RowNode>
        <SomeTag></SomeTag>
      </RowNode>
    </RootNode>'AS XML)
);

DECLARE @FirstXml XML=(SELECT YourXml FROM @tbl WHERE ID=1);
INSERT INTO @tbl VALUES(@FirstXml);
INSERT INTO @tbl SELECT @FirstXml;
INSERT INTO @tbl SELECT @FirstXml.query(N'.');

SELECT * FROM @tbl

The result

ID  YourXML
--FOR XML PATH created - why ever - both tags
1   <RootNode><RowNode><SomeTag></SomeTag></RowNode></RootNode>
--FOR XML AUTO and all implicit casts use self-closing tags
2   <RootNode><RowNode><SomeTag /></RowNode></RootNode>
3   <RootNode><RowNode><SomeTag /></RowNode></RootNode>
4   <RootNode><RowNode><SomeTag /></RowNode></RootNode>
--Here we insert the first node *as is*
5   <RootNode><RowNode><SomeTag></SomeTag></RowNode></RootNode>
6   <RootNode><RowNode><SomeTag></SomeTag></RowNode></RootNode>
--But `.query()` will reformat this
7   <RootNode><RowNode><SomeTag /></RowNode></RootNode>

Some background:

XML is not stored as string string representation you see but as a hierarchy-table. Whenever you get the XML displayed on screen, its string representation is rebuilt from scratch. This can be slightly different each time you call it (e.g. attribute's order, CDATA sections, empty elements).

If you need this you can only enforce this format on string level. You might use some kind of RegEx approach, to replace any <abc/> with <abc></abc>.

But again: You should not have to think about that...

UPDATE

Try the code above with this SELECT:

SELECT *
      ,CAST(YourXML AS NVARCHAR(MAX)) AS CastedToString
FROM @tbl

The result

ID  YourXML 
    CastedToString

    --implicitly changed to self-closing tags
1   <RootNode><RowNode><SomeTag></SomeTag></RowNode></RootNode> 
    <RootNode><RowNode><SomeTag/></RowNode></RootNode>

    --Self-closing **without a blank!!!**
2   <RootNode><RowNode><SomeTag /></RowNode></RootNode> 
    <RootNode><RowNode><SomeTag/></RowNode></RootNode>

You can see, that a comparisson on string level is not that easy... and rather hard to predict... You might use CAST(YourXml.query(N'.') AS NVARCHAR(MAX)) to get the same action done on each of your XMLs...

Collectives™ on Stack Overflow

Empty tags formatting in XML column in SQL Server

2 Answers 2

1 Comment

Some background:

UPDATE

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Some background:

UPDATE

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related