CODEWITHSUNDEEP
sql-serverxmlsqlxml
Liero
Liero

Reputation: 27328

Empty tags formatting in XML column in SQL Server

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?

Upvotes: 3

Views: 4804

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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

Upvotes: 0

Thom A
Thom A

Reputation: 95544

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?

Upvotes: 3

Related Questions