XML Source Problem

Question

XML Source Problem

Paul_Harvey

Hall of Fame

Points: 3822
More actions
December 7, 2009 at 4:50 am

#214554

Hi all,
When my source XML file contains an ampersand, it causes my package to fail.
I have trawled search engines, and there is a suggestion that this may be due to an encoding problem. The XML Source component has all errors set to "Redirect Row", but this still fails the component. The "progress" tab shows the component "was unable to read the XML data. An error occurred while parsing EntityName."
My current workaround is to manually find and replace the ampersand.
Is there a way that this could be automated? Currently the SSIS package cycles through XML files in a ForEach loop.
Any help gratefully received as always!
Paul

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

The problem is that ampersand is a reserved symbol in XML. It is used as part of escape characters and if it is used as part of text node or value for attribute you should use an escape character for it (&). In short, there is a good chance that the problem is that the XML that you are using is not a valid XML.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 2

Sure Adi.. I understand that. The ampersand characters come from either Company Name or Address fields that are placed into the XML file by my supplier.

This is not something that they are going to change for me unfortunately.

I was hoping that I could I could somehow put a step into my ForEach loops before the XML Source that would do a find and replace for the & characters.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

You can have a step that opens it as text file and replaces all ampersands with the escape characters that is used for ampersand (& a m p 😉 - Couldn't get it to write the escape character. In reality you should lose the brackets and write it without the spaces.

After this replace operation, the XML should be a valid one. I still think that if it was agreed that the other side should send you an XML file, then they should make it a valid one.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 4

Thanks Adi,

Yes I agree... but this has been ongoing for some time, and I am keen to prevent the overnight failures as soon as I can.

Could you please advise me on how I could go about implementing the step that you mention? It sounds like exactly what I am trying to do, but I do not know how to.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

Bellow is an SQL code that shows you how to use the replace function. The first select shows you how the XML looks (you will see the escape characters) and the second one shows that the value is a real ampersand and not the escape character.

declare @STR varchar(1000)

declare @xml xml

--String that can not be converted into xml

set @STR =

'<root>

<Store>Barns&Nobel</Store>

</root>'

set @STR = replace(@str, '&', '&')

select @xml = @STR

--See the xml with the escape character

select @xml

--see the value with the real ampersand

select @xml.value('(root/Store/text())[1]','varchar(15)')

Adi

edit - Can't seem to be able to show the correct code (the replace get modifies). You can view it in the attached file

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 6

Hi Adi,

I ran the attached file, and I can see that this achieves what I need.

Please forgive me though, and perhaps I am asking this question in the wrong place? Is there any way that I can do this on a file (rather than a specific string), or even better, a variable in SSIS?

I am quite new to this, so if I should have posted my question somewhere else, then I apologise.

Paul

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 7

I’m sorry, but I’m not a good source when it comes to SSIS. I suggest that you’ll post a new question about how replace characters in a file before working with them. In any case don’t use this thread, because the original question has nothing to do with the new question.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 8

Paul_Harvey

Hall of Fame

Points: 3822

December 7, 2009 at 8:10 am

#1088564

I'll do that.

Thanks for your time.