CODEWITHSUNDEEP
xmlsql-server-2008
Deepak.Aggrawal
Deepak.Aggrawal

Reputation: 1277

Self-closing tags in XML in SQL Server

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.

Upvotes: 1

Views: 8455

Answers (3)

Riley Major
Riley Major

Reputation: 2014

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

Upvotes: 1

Deepak.Aggrawal
Deepak.Aggrawal

Reputation: 1277

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>

Upvotes: 4

rekire
rekire

Reputation: 47945

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.

Upvotes: 1

Related Questions