XML error in sql server

Question

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.

Then it won't work because you try to convert an XML data type implicitly into a varchar which isn't defined. If you need to access the XML as an XML data type in the constructed query, you need to pass it, select it or otherwise construct it within the query you're trying to create. — Adwaenyth
– Adwaenyth, Commented May 2, 2016 at 10:14
Please explain, how you call this. From your last question I take, that you have a bigger XML where you pick one node with its children and then you pass this to SQL. Just wirte SELECT @Query instead of EXEC(@Query) and you will see, that the query is not the way you expect it to be... So once again: Please tell us how you call this from C# and how you handle this in SQL. — Gottfried Lesigang
– Gottfried Lesigang, Commented May 2, 2016 at 10:18

har07 · Accepted Answer · 2016-05-02 11:17:51Z

2

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)

edited May 2, 2016 at 11:17

answered May 2, 2016 at 10:24

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

I think, you magic glass bulb points in the right direction (+1 from my side), but my question was, why there's need of dynamic SQL and sp_executesql at all?

har07 Over a year ago

@Shnugo You're right. The SQL that OP posted can be done without dynamic SQL at all (updated my answer to include this point). Maybe there is portion of the dynamic SQL that isn't show in question which forced him to go that route, not sure....

Collectives™ on Stack Overflow

XML error in sql server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related