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.

OLE-Db connection in Transformation Script Component

Hello,

Using the following documentation as a guide:

http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx

I instantiated a new script component into an existing Data Flow in my SSIS project.

In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.

My Connection Manager is of the type oOLEDB.

I then opened up the script designer and added the following lines of code where it said "Add your code here"

Dim myConnManager As IDTSConnectionManager90 = _

Me.Connections.ECFconnection

Dim dbConn As OleDb.OleDbConnection = _

CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)

When I test run the project I get the following error and the new script component is red:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

I know the database connection works since I am using it in a component that executes before this new script component.

I am stuck...Any suggestions?

Unfortunately you cannot use a Connection Manager that returns a native type in managed code. This includes OLE DB and Excel.

This limitation is noted in BOL in
http://msdn2.microsoft.com/en-us/library/ms136018.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337080.aspx

with this suggestion:

If you need to call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel workbook, and then enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

-Doug

|||

Thank you. That was the nudge in the right direction that I needed.

Greg.

OLE-Db connection in Transformation Script Component

Hello,

Using the following documentation as a guide:

http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx

I instantiated a new script component into an existing Data Flow in my SSIS project.

In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.

My Connection Manager is of the type oOLEDB.

I then opened up the script designer and added the following lines of code where it said "Add your code here"

Dim myConnManager As IDTSConnectionManager90 = _

Me.Connections.ECFconnection

Dim dbConn As OleDb.OleDbConnection = _

CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)

When I test run the project I get the following error and the new script component is red:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

I know the database connection works since I am using it in a component that executes before this new script component.

I am stuck...Any suggestions?

Unfortunately you cannot use a Connection Manager that returns a native type in managed code. This includes OLE DB and Excel.

This limitation is noted in BOL in
http://msdn2.microsoft.com/en-us/library/ms136018.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337080.aspx

with this suggestion:

If you need to call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel workbook, and then enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

-Doug

|||

Thank you. That was the nudge in the right direction that I needed.

Greg.