troubleshooting Question

Strange behaviour of sp_executesql with xml parameter

Avatar of J2015
J2015 asked on
Microsoft SQL Server 2008Microsoft SQL Server 2005
7 Comments1 Solution470 ViewsLast Modified:
I am getting various results from 'EXEC sp_executesql @sql, N'@xml xml', @xml', depending on how I construct the @sql variable. Only 2 of these constructs are successful. All others fail, with different error messages. Whether successful or not, the only thing that changes is the number of repetitions of a single field in the SELECT clause, and whether they are on a single line, or separated by carriage returns.

The general construct is:

   SET @sql='
   SELECT
   (repeated field)
   FROM @xml.nodes(''/row'') As t(c)
   INNER JOIN [SQLRONA].[dbo].[T_item] ON [SQLRONA].[dbo].[T_item].[CodeRona]=t.c.value(''@sk'',''char(8)'')'

First, here are the 2 field repetitions that work:

   1) [SQLRONA].[dbo].[T_item].[CodeRona] (N.B.: Not a repetition. Just a single field.)
   2) [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona]

Following are some of the failing repetitions, and their error messages:

   3) [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona]
       Incorrect syntax near '('.

   4) [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona]
       Unclosed quotation mark after the character string 'Code'. Incorrect syntax near 'Code'.

   5) [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona]
       An expression of non-boolean type specified in a context where a condition is expected, near 'SQLRONA'.

   6) [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona]
       Unclosed quotation mark after the character string 'db'. Incorrect syntax near 'db'.
       
Can anyone explain why sp_executesql is behaving like that?
ASKER CERTIFIED SOLUTION
J2015

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros