SQL - Exporting a table with xml colomun into a text file

Question

I need to export a table, that contains a XML column (this xml my contain any special characters, so I can not use them as column delimiters) into a text file.

I am using SQL Server 2014. The XML column may contain special characters like @, |, ,, ?, tab, <cr> many things that could be used as delimiters.

I want to export the whole table. The XML is not structured internally. The max length of the column is around 6000 characters. The table has around 700k rows. And the destination is the same table in a SQL Server 2012 (lower version than the origin), they are in different networks.

I am trying to export it as a .txt file with || as column delimiter. But when I try to import this file into the destination table, it says that the text was truncated and could not be imported.

What's the best way I can do this?

Answer 1

All your answers you gave in your comments give me the idea that you are going the wrong way... The best approach should be linked servers:

Read here: https://msdn.microsoft.com/en-us/library/ff772782.aspx?f=255&MSPPError=-2147217396

Further information here: https://msdn.microsoft.com/en-us/library/ms190479.aspx?f=255&MSPPError=-2147217396

Try this in your SS2014

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
    @server = N'YourLowerServer', 
    @srvproduct=N'SQL Server' ;
GO

This you need to get access:

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'YourLowerServer', 
    @locallogin = NULL , 
    @useself = N'True' ;
GO

If this is done you can use the INSERT INTO from one server directly to the other server. Try this in your SS2014:

INSERT INTO YourLowerServer.YourDatabase.dbo.TableName(col1,col2,...)
SELECT col1,col2,... FROM dbo.TableName 

If you want to get rid of your linked server after this operation use sp_dropserver (read here: https://msdn.microsoft.com/en-us/library/ms174310.aspx)

Hope this helps...