Omitted characters after importing XML file with SQLXMLBulkLoad

When importing data in my C# application with the SQLXML4's SQLXMLBulkLoad library, I noticed that if my data has 2 ampersands seperated by space, it will be imported into the SQL table without the spaces. It doesn't matter how many spaces are there, it will remove all of them. I have verified that the spaces exist in the XML file and the ampersands are properly escaped as "&".

Example:

XML Field Value: '& &'
Expected SQL Field Value: '& &'
Resulting SQL Field Value: '&&'

Let me know if you need any additional information.

EDIT: I apologize if this is in the wrong forum. Since it is related to SQL XML Bulkload, I felt this was a better place than the C# forum.
Have I posted this in the wrong forum? I need an answer to this soon.
|||

Hello,

I wasn't able to repro your problem.Here is the xmldata I used:

<test xmldata="&amp; &amp;"/>

and this is what I get in the database:

xmldata

--

& &

If I run this query in QA:

select * from test for xml auto

I get back:

<test xmldata="&amp; &amp;"/>

Could you send me the schema and the data file you used?

Thanks,

Monica

|||One difference I see in the xml sample you used is I am using an actual value instead of an attribute. For example:

<Document>&amp; &amp;</Document>

This field (Document) is going into the SQL table's field name [Document] which has its data type set as text. I have not tried importing it into a varchar to see if that makes a difference as that is not really an option I have.
|||

Hello,

I was able to repro your problem and it looks like this is the current behavior.

The solution for preserving the whitespace would be to wrap the data inside a CDATA element.

I hope this helps.

Best regards,

Monica

|||holuilhello! how can i use the SQLXMLBulkLoad in c#? when i reference the microsoft.data.xml namespace, i can not use the SQLXMLBulkLoad.hello! how can i use the SQLXMLBulkLoad in c#? when i reference the microsoft.data.xml namespace, i can not use the SQLXMLBulkLoad.

Omitted characters after importing XML file with SQLXMLBulkLoad

When importing data in my C# application with the SQLXML4's SQLXMLBulkLoad library, I noticed that if my data has 2 ampersands seperated by space, it will be imported into the SQL table without the spaces. It doesn't matter how many spaces are there, it will remove all of them. I have verified that the spaces exist in the XML file and the ampersands are properly escaped as "&amp;".

Example:

XML Field Value: '&amp; &amp;'
Expected SQL Field Value: '& &'
Resulting SQL Field Value: '&&'

Let me know if you need any additional information.

EDIT: I apologize if this is in the wrong forum. Since it is related to SQL XML Bulkload, I felt this was a better place than the C# forum.
Have I posted this in the wrong forum? I need an answer to this soon.
|||

Hello,

I wasn't able to repro your problem.Here is the xmldata I used:

<test xmldata="&amp; &amp;"/>

and this is what I get in the database:

xmldata

--

& &

If I run this query in QA:

select * from test for xml auto

I get back:

<test xmldata="&amp; &amp;"/>

Could you send me the schema and the data file you used?

Thanks,

Monica

|||One difference I see in the xml sample you used is I am using an actual value instead of an attribute. For example:

<Document>&amp; &amp;</Document>

This field (Document) is going into the SQL table's field name [Document] which has its data type set as text. I have not tried importing it into a varchar to see if that makes a difference as that is not really an option I have.
|||

Hello,

I was able to repro your problem and it looks like this is the current behavior.

The solution for preserving the whitespace would be to wrap the data inside a CDATA element.

I hope this helps.

Best regards,

Monica

|||holuilhello! how can i use the SQLXMLBulkLoad in c#? when i reference the microsoft.data.xml namespace, i can not use the SQLXMLBulkLoad.hello! how can i use the SQLXMLBulkLoad in c#? when i reference the microsoft.data.xml namespace, i can not use the SQLXMLBulkLoad.sql

Omitted characters after importing XML file with SQLXMLBulkLoad

When importing data in my C# application with the SQLXML4's SQLXMLBulkLoad library, I noticed that if my data has 2 ampersands seperated by space, it will be imported into the SQL table without the spaces. It doesn't matter how many spaces are there, it will remove all of them. I have verified that the spaces exist in the XML file and the ampersands are properly escaped as "&amp;".

Example:

XML Field Value: '&amp; &amp;'
Expected SQL Field Value: '& &'
Resulting SQL Field Value: '&&'

Let me know if you need any additional information.

EDIT: I apologize if this is in the wrong forum. Since it is related to SQL XML Bulkload, I felt this was a better place than the C# forum.
Have I posted this in the wrong forum? I need an answer to this soon.
|||

Hello,

I wasn't able to repro your problem.Here is the xmldata I used:

<test xmldata="&amp; &amp;"/>

and this is what I get in the database:

xmldata

--

& &

If I run this query in QA:

select * from test for xml auto

I get back:

<test xmldata="&amp; &amp;"/>

Could you send me the schema and the data file you used?

Thanks,

Monica

|||One difference I see in the xml sample you used is I am using an actual value instead of an attribute. For example:

<Document>&amp; &amp;</Document>

This field (Document) is going into the SQL table's field name [Document] which has its data type set as text. I have not tried importing it into a varchar to see if that makes a difference as that is not really an option I have.
|||

Hello,

I was able to repro your problem and it looks like this is the current behavior.

The solution for preserving the whitespace would be to wrap the data inside a CDATA element.

I hope this helps.

Best regards,

Monica

|||holuilhello! how can i use the SQLXMLBulkLoad in c#? when i reference the microsoft.data.xml namespace, i can not use the SQLXMLBulkLoad.hello! how can i use the SQLXMLBulkLoad in c#? when i reference the microsoft.data.xml namespace, i can not use the SQLXMLBulkLoad.