tags:

views:

232

answers:

1

I am trying to insert a value to sql_variant column in sql server from an xml column

ex.

 INSERT INTO 
  [dbo].[TestColumn]
 (
  Id,
  Attribute,
  AttributeValue
 )
 SELECT 
  Id, 
  'TestName', 
  CAST(CustomColumns.query('//TestName') AS nVarchar(MAX))
 FROM 
  [dbo].[Clmnt] (NOLOCK) 

I got this error,

Operand type clash: nvarchar(max) is incompatible with sql_variant

Anybody has any clue on this, How do I easily insert a value in sql_variant?

+3  A: 

sql_variant can not store nvarchar(max)

sql_variant (Transact-SQL)

That link says that the following the types of values cannot be stored by using sql_variant:

varchar(max)
varbinary(max)
nvarchar(max)
xml
text
ntext
image
timestamp
sql_variant
geography
hierarchyid
geometry
User-defined types
KM
Agreed. So how I can insert data?
Tamil.SQL
do not use a sql_variant column, perhaps nvarchar(max) or even xml. Although it does appear that you are using an value-attribute design, you don't explain your schema in the original question, so it is a little hard to expand.
KM
you could try: `CAST(CustomColumns.query('//TestName') AS nVarchar(8000))`, which will only work if your XML is under 8000 characters.
KM
varchar(max) is not working limitation to Varchar(8012). Thanks for your great help.
Tamil.SQL