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.

OLTP and Reporting databases seperated?

we are using an object database for our OLTP but for reporting we havegot some issues about performance as the cpu becames a bottleneck.Andwe want to be able torun on low end computers...

One of our team members suggested to replicate the object database to aSQL table.But just a single one.The most denormalized thing ever.(358 coloumns)

is this the fastest way we can get in reporting?

*we don't want harddisk,ram or cpu to became a bottleneck. ( must run on cheap staff)

Replicating to SQL Server from object database is heterogeneous replication and replication is one of those things in SQL Server that is work in progress, so you may not get the results you expect. If you want to remain denormalized you can still reduce the columns by creating UNION ALL Views, if you choose to normalize the table start at the lowest level files and association because what you have now is just a flat file. Hope this helps.

|||thanks.I guess RDBMS is the fastest way to present table to UI.