SSIS ETL: Xml column into varchar destination

Question

What would be the optimal and efficient way to do the below

Requirement

Copy an xml column into staging DB and then
Staging to DWH :

Column Size
Source : Xml
StagingDB : Xml or Varchar ?
Datawarehouse : Varchar(8000)

I need to decide which would be the best optimal and most performing way to copy an xml column into staging DB. copy XML from source into an xml column in STage DB is the best way or Xml to Varchar(max) will be the best way, considering the data transferred will be in millions ?

Answer 1

If you don't want to benefit from Xml properties and you want to store the whole xml inside a Varchar(8000) column in the data warehouse it is more easier to read the xml as a text. (less validation required , faster)

Note that you can read the file using a script task or other component instead of Xml Source

So it is up to you, if you need to get some xml node , or to achieve something related to xml functions or properties then you have to use Xml Else use Varchar.