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.

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> </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> </Space>' SELECT @x

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

Naomi Nosonovsky 8,291

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:

User's image

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.

Yitzhak Khabinsky 26,471 Reputation points

2025-03-11T02:35:29+00:00

I would suggest a different approach.

It is better to use SSIS XML Task, operation XSLT transformation instead of XPath. That way you have much more control over the output XML.
Naomi Nosonovsky 8,291 Reputation points

2025-03-11T19:04:42.3966667+00:00

I changed the option to XLST from XPATH and the extracts never finished, so I restored the original package

Accepted answer

Erland Sommarskog 119.3K Reputation points MVP

2025-03-10T22:16:34.5666667+00:00
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> </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> </Space>' SELECT @x
Please sign in to rate this answer.
Naomi Nosonovsky 8,291 Reputation points

2025-03-10T23:17:39.0933333+00:00

I like your last idea, I think we should try it. Today I re-wrote the package to use C# script to write XML instead of XML task, but I doubt it will help. I'll pass the info to my colleague.

Erland Sommarskog 119.3K Reputation points MVP

2025-03-11T21:14:30.8+00:00

Sorry, for posting half asleep. It is the wrong answer altogether. Your real error is that you are using element-centric XML. Not only does it lead to problems like this one, but the XML takes up a lot more space.

Use attribute-centric XML instead, and it is a piece of cake:

<Names FirstName="Naomi" MiddleName="" LastName="Nosonovsky" />

Viorel 120.4K Reputation points

2025-03-11T21:29:29.8933333+00:00

This XML preserves the spaces too:

declare @x xml = '<MiddleName xml:space="preserve"> </MiddleName>'

Maybe it can be used in SSIS too. The xml:space attribute can be specified for parent or root node.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

0 additional answers

Your answer