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!

Not sure if there's a programmatic side to this as well, if so you might consider taking said data and compressing it first, and then passing it through as binary data? Don't know if this is viable or not in your case though... — user2366842
– user2366842, Commented Dec 4, 2014 at 19:55
There is sth wrong with the query you have posted: 22nd argument: destinationport -> DESTINATIONCHANNEL (?) — Giorgos Betsos
– Giorgos Betsos, Commented Dec 4, 2014 at 19:57

Giorgos Betsos · Accepted Answer · 2014-12-04 20:05:21Z

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.

answered Dec 4, 2014 at 20:05

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Giorgos Betsos Over a year ago

@Kevin_S You' re welcome. You can accept this post as an answer to your question, if it really helped you solve your ploblem, so that other people having a similar problem might benefit from this post.

Jason White · Accepted Answer · 2014-12-04 19:34:18Z

0

Perhaps you could change this:

varchar(8000)

to this:

varchar(max)

answered Dec 4, 2014 at 19:34

Jason White

2181 silver badge5 bronze badges

1 Comment

Kevin_S Over a year ago

I tried that first with the same result. Also, most of the the data in the CIRCUITPATH AND CIRCUITPROTECTPATH fields are between 100 and 600 characters in length.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related