Reputation: 15
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!
Upvotes: 0
Views: 2423
Reputation: 72235
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.
Upvotes: 1
Reputation: 218
Perhaps you could change this:
varchar(8000)
to this:
varchar(max)
Upvotes: 0