question

sq84 avatar image
sq84 asked

XML import to SQL server truncates data

I am writing below script to import the data to sql server SELECT * INTO TestTable FROM OPENXML (@idoc, '/TEST/ChkEvent',1) WITH (UnqID VARCHAR(25)'../../../../UnqID', AdDate VARCHAR(25)'../../@AdDate', TestDesc VARCHAR(1)) During this the data truncates for AdDate or TestDesc fields. I know if I use varchar(max) problem will solve. But do we have any method through which we can catch this error and prevent sql server from truncating data. I have tried try catch block but it didn't resolve it.
xml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Dave_Green avatar image
Dave_Green answered
This silent truncation is a fact of life with varchar fields - however you are not the first person to note that this isn't perhaps desirable in all circumstances. There's been a [connect item on a very similar specific issue][1] and Erland Sommarskog has written a [post with more details][2] about how a strict check system might work. The [MSDN documentation][3] states: "When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated". This is the crux of the issue you are seeing, and I'm not aware of a way to trap it or prevent the behaviour. You could use varchar fields, then check the length of each and alter TestTable / raise an error after loading the data, allowing you to change to non-max varchar fields. However, there may be another way of resolving your issue. Does your XML document have a schema definition that you can store in SQL which specifies the maximum size of your fields? If so, you might check the document against the schema, then you can be sure what size varchar fields to use when loading into SQL Server to prevent truncation. There's a [post on Pinal Dave's blog][4] about validating XML to a schema, and a [more generic version on Simple Talk][5]. If your XML document doesn't match your schema, this should generate a trappable error which you can then use to reject the data. If your XML definition can change at runtime, you could consider using CLR to validate it per [this stack overflow post][6]. [1]: http://connect.microsoft.com/SQLServer/feedback/details/622699/stored-procedures-should-throw-error-message-when-called-with-string-values-which-exceed-lenght-of-stored-procedure-parameter [2]: http://www.sommarskog.se/strict_checks.html [3]: http://msdn.microsoft.com/en-us/library/ms176089.aspx [4]: http://blog.sqlauthority.com/2009/12/02/sql-server-validate-an-xml-document-in-tsql-using-xsd-by-jacob-sebastian/ [5]: http://www.simple-talk.com/sql/learn-sql-server/introduction-to-xml-schema/ [6]: http://stackoverflow.com/questions/10693123/validate-xml-using-xsd-specified-at-runtime-in-sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.