CODEWITHSUNDEEP
sql-serverxml
user2998990
user2998990

Reputation: 980

XML error in sql server

Following is the query that I am executing. I am getting XML input from c# in @XMLdata parameter. 

CREATE  TABLE  #TablesList
(
    TableName VARCHAR(500),
    RefTable VARCHAR(500),
    RefTableIDColumn VARCHAR(500)
)


SET @Query = @Query + ' INSERT INTO #TablesList SELECT ref.value(''tablename[1]'',''nvarchar(500)'') AS tablename,'
SET @Query = @Query + ' ref.value(''refTable[1]'',''nvarchar(500)'') AS refTable, ref.value(''refTableIDColumn[1]'',''nvarchar(500)'') AS refTableIDColumn FROM '
SET @Query = @Query + @XMLdata+'.nodes(''//Table[@name="'+@DataItem+'"]'') AS R(ref)'
EXEC(@Query)

When I execute the query, I get the following error. Error is for the 2nd last line

The data types varchar(max) and xml are incompatible in the add operator.

Upvotes: 0

Views: 105

Answers (1)

har07
har07

Reputation: 89325

Instead of injecting content of @XMLdata into your dynamic SQL string, try to pass @XMLdata as parameter to sp_executesql :

.....
SET @Query = @Query + '@XMLdata.nodes(''//Table[@name="'+@DataItem+'"]'') AS R(ref)'
EXEC sp_executesql @Query, N'@XMLdata XML', @XMLdata

As far as dynamic SQL posted in the question is concerned, you can avoid using dynamic SQL completely. The only 'dynamic' part of the dynamic SQL comes from value of @DataItem variable, which can be handled casually using sql:variable(), as follow :

INSERT INTO #TablesList 
SELECT 
    ref.value('tablename[1]','nvarchar(500)') AS tablename,
    ref.value('refTable[1]','nvarchar(500)') AS refTable, 
    ref.value('refTableIDColumn[1]','nvarchar(500)') AS refTableIDColumn 
FROM @XMLdata.nodes('//Table[@name=sql:variable("@DataItem")]') AS R(ref)

Upvotes: 2

Related Questions