Self-closing tags in XML in SQL Server

Question

I am using the SQL Query to return the result as XML using in SQL server. I am using the below SQL Query-

SELECT '1' AS [Column1]
UNION
SELECT '2' AS [Column2]
UNION
SELECT '' AS [Column3]
FOR XML PATH('Test'), ROOT('Rows')

Result XML:

  <Rows>
    <Test>
      <Column1></Column1>
    </Test>
    <Test>
      <Column1>1</Column1>
    </Test>
    <Test>
      <Column1>2</Column1>
    </Test>
  </Rows>

Output would be like :

  <Rows>
    <Test>
      <Column1/>
    </Test>
    <Test>
      <Column1>1</Column1>
    </Test>
    <Test>
      <Column1>2</Column1>
    </Test>
  </Rows>

Any help would be appreciate. Thanks in Advance.

What's the issue ? Why is the result you're getting not OK for you? — marc_s
– marc_s, Commented Dec 21, 2012 at 6:09
Actually in XML if a Tag has no value then it display like: <Column1></Column1> but I want to display as like this <Column1/>. — Deepak.Aggrawal
– Deepak.Aggrawal, Commented Dec 21, 2012 at 6:46
Those two representations are semantically the same - any XML parser should understand both - so again: what is the real issue here? Just "cosmetics" ? — marc_s
– marc_s, Commented Dec 21, 2012 at 6:49
Yes, they are identically same but it take more space in size. XML are larger in size, So I need to display as a self closing tag — Deepak.Aggrawal
– Deepak.Aggrawal, Commented Dec 21, 2012 at 6:51

Deepak.Aggrawal · Accepted Answer · 2012-12-21 09:02:50Z

4

Actually If we inner the XML then it will return the Tag as self closing. Please see the below SQL code:

DECLARE @TempData Table
(
Column1 NVARCHAR(250)
)
INSERT INTO @TempData values('Column1')
INSERT INTO @TempData values('Column2')
INSERT INTO @TempData values('')
SELECT
(
SELECT * FROM @TempData FOR XML PATH('Test'), Type
)
For XML PATH (''),
ROOT('Rows')

Output:

<Rows>
  <Test>
    <Column1>Column1</Column1>
  </Test>
  <Test>
    <Column1>Column2</Column1>
  </Test>
  <Test>
    <Column1 />
  </Test>
</Rows>

answered Dec 21, 2012 at 9:02

Deepak.Aggrawal

1,27711 silver badges24 bronze badges

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

Comments

rekire · Accepted Answer · 2012-12-21 07:16:40Z

1

I think the result is correct you are selecting an empty string try selecting null:

SELECT '1' AS [Column1]
UNION
SELECT '2' AS [Column2]
UNION
SELECT NULL AS [Column3]
FOR XML PATH('Test'), ROOT('Rows')

Result:

<Rows>
  <Test />
  <Test>
    <Column1>1</Column1>
  </Test>
  <Test>
    <Column1>2</Column1>
  </Test>
</Rows>

That is even much less as you want.

Because you don't like to remove also the tag try this here:

declare @output nvarchar(max)

declare @XML xml = '
  <Rows>
    <Test>
      <Column1></Column1>
    </Test>
    <Test>
      <Column1>1</Column1>
    </Test>
    <Test>
      <Column1>2</Column1>
    </Test>
  </Rows>'

set @output = replace(cast(@XML as nvarchar(max)), '<Column1></Column1>', '<Column1/>')

select @output

Sorry for hard encoding your xml result I cannot figger out right now how to set the output into @XML dynamically.

edited Dec 21, 2012 at 7:16

answered Dec 21, 2012 at 5:54

rekire

48.1k34 gold badges184 silver badges279 bronze badges

6 Comments

Deepak.Aggrawal Over a year ago

Result is correct but it will take more space so if a Tag has no value then I need to display like: <Column1/>.

rekire Over a year ago

Did you check my idea? See the result of my test.

Deepak.Aggrawal Over a year ago

Yes, I see but this will remove column1 Tag. I need this in XML.

rekire Over a year ago

If you get it manged to put the result in the variable @XML try this here: replace(cast(@XML as nvarchar(max)), '<Column1></Column1>', '<Column1/>').

Deepak.Aggrawal Over a year ago

Actually I have not a single column in my actual XML. Thanks for your help but Now I have found the solution.

|

Community · Accepted Answer · 2017-05-23 11:58:19Z

First, as mentioned by @marc-s in the comments, both representations are semantically equivalent according to the XML spec.

The representation of an empty element is either a start-tag immediately followed by an end-tag, or an empty-element tag.

http://www.w3.org/TR/REC-xml/#NT-content

See also:

https://stackoverflow.com/a/2279530/2266979

In the comments, you clarified that the self-closing tag representation involves fewer characters.

That only matters when consuming the XML outside of SQL Server, since in the server it "is stored internally in a binary format" (https://technet.microsoft.com/en-US/library/ms345115(v=SQL.90).aspx).

If you are consuming the XML outside of SQL Server, where these characters would matter, you have probably converted it to text. However, when you do that, SQL Server (2008, anyway) automatically uses self-closing tags.

For example:

SELECT CONVERT(varchar(max),CONVERT(xml,'<tag></tag>'));

Will return:

<tag/>

So all you should need to do is convert your final result to text.

This assumes that you have typed XML. If you create your XML with FOR XML PATH without specifying the TYPE keyword, you are essentially just producing text which looks like XML.

So this:

SELECT CONVERT(varchar(max),(SELECT '' AS 'tag' FOR XML PATH('')));

Will return:

<tag></tag>

If you instead use the TYPE keyword:

SELECT CONVERT(varchar(max),(SELECT '' AS 'tag' FOR XML PATH(''), TYPE));

You will get:

<tag/>

Collectives™ on Stack Overflow

Self-closing tags in XML in SQL Server

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related