SSIS XML write file task - how to preserve empty values?

Question

Hi,

We have SSIS package that writes XMLs into files. We have stored procedure that generates XML and outputs it as XML with tags like this <MiddleName> </MiddleName>. In other words, there is a space (intentional) inside the tags. We have XML task in the package configured like this:

The problem is that resulting XML ends up being like this </MiddleName> and the space is gone. We need it to preserve spaces. Is there a way with the XML.write task or what should we do to write the XML the way we generated it in the stored procedure?

Thanks in advance.

Answer 1

That's how XML works, I guess. The same thing happens in SQL Server:

DECLARE @x xml = '<Space> </Space>'
SELECT @x

This results in:

<Space />

If you want a space there, you to enticise it:

DECLARE @x xml = '<Space>&#32;</Space>'
SELECT @x

This gives you:

<Space> </Space>

But then this will become <Space /> in the next round.

I think a better solution is to use a hard space (char(160):

DECLARE @x xml = '<Space>&#160;</Space>'
SELECT @x