CODEWITHSUNDEEP
sql-serverxmlsql-server-2005for-xml
Andreas Ågren
Andreas Ågren

Reputation: 3929

Truly empty element with sql server for xml-directive

How do I create a truly empty xml element with the for xml-directive in sql server (2005)?

Example:

select
    ''
for xml path('element'), root('elements')

Outputs:

<elements><element></element></elements>

But what I really want is:

<elements><element /></elements>

Upvotes: 1

Views: 4656

Answers (3)

Leonardo E
Leonardo E

Reputation: 11

You only need to know if the value is null, for ex: ISNULL(Field, '')

Like this:

DECLARE @T table
(
  ID int identity primary key,
  Name nvarchar(10)
)
INSERT INTO @T (Name)
    SELECT 'Name1'
    UNION ALL
    SELECT NULL

-- @Result1
SELECT  ID AS Id
        ,ISNULL(Name, '') AS Name
FROM @T
FOR XML PATH ('Person')

-- @Result2
DECLARE @Result2 XML = ( SELECT
                            ID AS Id
                            ,ISNULL(Name, '') AS Name
                        FROM @T
                        FOR XML PATH ('Person'))
SELECT @Result2;

Result1:

<Person>
  <Id>1</Id>
  <Name>Name1</Name>
</Person>
<Person>
  <Id>2</Id>
  <Name></Name>
</Person>

Result2:

<Person>
  <Id>1</Id>
  <Name>Name1</Name>
</Person>
<Person>
  <Id>2</Id>
  <Name />
</Person>

Upvotes: 1

guille
guille

Reputation: 256

select
    null
for xml path('element'), root('elements')

Upvotes: 0

Andreas &#197;gren
Andreas &#197;gren

Reputation: 3929

Add the type directive and then run it in a subquery.

Like this:

select
(
    select
        ''
    for xml path('element'), type
)
for xml path('elements')

Upvotes: 1

Related Questions