SQL Server "String or binary data would be truncated" XML Insert

Question

I am relatively new to SQL Server. Most of my background is MS Access. I have a stored procedure that takes an XML file and inserts the node information into 16 different tables. I am getting a "String or binary data would be truncated" error. Here is the piece of the code that is causing the issue:

DECLARE @xml XML
DECLARE @filename varchar(255);

Select @filename = 'SilvxInSightImport_uslsss17_14-08-24_20-40-00.xml'
--Select @filename = @filepath

Select @xml = 
CONVERT(XML,bulkcolumn,2) FROM OPENROWSET(BULK 'C:\Users\Kevin.Smith\Documents\TDAT\CPOFiles\SilvxInSightImport_uslsss17_14-08-31_20-40-01.xml',SINGLE_BLOB) AS X


Insert into [SILVX_CIRCUIT]
(
FileName, md_group,pkey,s_state,circuit_name,admin,protection,vpnid,sourcereroute,trunkrestoration,fwdbw,
sourcetype,sourceip,sourcechassis,sourceslot,sourceport,sourcechannel,destinationtype,
destinationip,destinationchassis,destinationslot,destinationport,destinationchannel,circuitpath,
reversion,cg_net_instance,parent_hpkey
)

select @filename,
       T.X.value('(MD_GROUP/text())[1]', 'nvarchar(255)') as MD_GROUP,
       T.X.value('(PKEY/text())[1]', 'nvarchar(255)') as PKEY,
       T.X.value('(S_STATE/text())[1]', 'nvarchar(255)') as S_STATE,
       T.X.value('(CIRCUIT_NAME/text())[1]', 'nvarchar(255)') as CIRCUIT_NAME,
       T.X.value('(ADMIN/text())[1]', 'nvarchar(255)') as ADMIN,
       T.X.value('(PROTECTION/text())[1]', 'nvarchar(255)') as PROTECTION,
       T.X.value('(SOURCEREROUTE/text())[1]', 'nvarchar(255)') as SOURCEREROUTE,
       T.X.value('(TRUNKRESTORATION/text())[1]', 'nvarchar(255)') as TRUNKRESTORATION,
       T.X.value('(FWDBW/text())[1]', 'nvarchar(255)') as FWDBW,
       T.X.value('(SOURCETYPE/text())[1]', 'nvarchar(255)') as SOURCETYPE,
       T.X.value('(SOURCEIP/text())[1]', 'nvarchar(255)') as SOURCEIP,
       T.X.value('(SOURCECHASSIS/text())[1]', 'nvarchar(255)') as SOURCECHASSIS,
       T.X.value('(SOURCESLOT/text())[1]', 'nvarchar(255)') as SOURCESLOT,
       T.X.value('(SOURCEPORT/text())[1]', 'nvarchar(255)') as SOURCEPORT,
       T.X.value('(SOURCECHANNEL/text())[1]', 'nvarchar(255)') as SOURCECHANNEL,
       T.X.value('(DESTINATIONTYPE/text())[1]', 'nvarchar(255)') as DESTINATIONTYPE,
       T.X.value('(DESTINATIONIP/text())[1]', 'nvarchar(255)') as DESTINATIONIP,
       T.X.value('(DESTINATIONCHASSIS/text())[1]', 'nvarchar(255)') as DESTINATIONCHASSIS,
       T.X.value('(DESTINATIONSLOT/text())[1]', 'nvarchar(255)') as DESTINATIONSLOT,
       T.X.value('(DESTINATIONPORT/text())[1]', 'nvarchar(255)') as DESTINATIONPORT,
       T.X.value('(DESTINATIONCHANNEL/text())[1]', 'nvarchar(255)') as DESTINATIONCHANNEL,
       **T.X.value('(CIRCUITPATH/text())[1]', 'varchar(8000)')  as CIRCUITPATH,
       T.X.value('(CIRCUITPROTECTPATH/text())[1]', 'varchar(8000)') as CIRCUITPROTECTPATH,**
       T.X.value('(REVERSION/text())[1]', 'nvarchar(255)') as REVERSION,
       T.X.value('(CG_NET_INSTANCE/text())[1]', 'nvarchar(255)') as CG_NET_INSTANCE,
       T.X.value('(PARENT_HPKEY/text())[1]', 'nvarchar(255)') as PARENT_HPKEY

from @XML.nodes('/SilvxInSightImport/Tables/Table[@Name = "Circuit"]/TableData/Row') as T(X)

The bolded code is the issue. I have checked the destination table (SILVX_CIRCUIT) and changed the datatypes for CIRCUITPATH and CIRCUITPROTECTPATCH fields to VARCHAR(8000) but I still get the error. If I set the above from varchar(8000) to varchar(255) it runs without error, but truncates the data to a length of 255 as you might expect. I am not sure what I am doing wrong. Thank you in advance for helping a noob!

Answer 1

If you examine carefully how the columns in the INSERT statement match with the columns being selected by the SELECT statement then you will realize that CIRCUITPATH is being inserted in place of destinationchannel column.

destinationchannel is NVARCHAR(255), whereas CIRCUITPATH is VARCHAR(8000), so you get an error.

Answer 2

Perhaps you could change this:

varchar(8000)

to this:

varchar(max)