asked on
Strange behaviour of sp_executesql with xml parameter
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].[
First, here are the 2 field repetitions that work:
1) [SQLRONA].[dbo].[T_item].[
2) [SQLRONA].[dbo].[T_item].[
Following are some of the failing repetitions, and their error messages:
3) [SQLRONA].[dbo].[T_item].[
Incorrect syntax near '('.
4) [SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
Unclosed quotation mark after the character string 'Code'. Incorrect syntax near 'Code'.
5) [SQLRONA].[dbo].[T_item].[
An expression of non-boolean type specified in a context where a condition is expected, near 'SQLRONA'.
6) [SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
Unclosed quotation mark after the character string 'db'. Incorrect syntax near 'db'.
Can anyone explain why sp_executesql is behaving like that?
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].[
First, here are the 2 field repetitions that work:
1) [SQLRONA].[dbo].[T_item].[
2) [SQLRONA].[dbo].[T_item].[
Following are some of the failing repetitions, and their error messages:
3) [SQLRONA].[dbo].[T_item].[
Incorrect syntax near '('.
4) [SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
Unclosed quotation mark after the character string 'Code'. Incorrect syntax near 'Code'.
5) [SQLRONA].[dbo].[T_item].[
An expression of non-boolean type specified in a context where a condition is expected, near 'SQLRONA'.
6) [SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
[SQLRONA].[dbo].[T_item].[
Unclosed quotation mark after the character string 'db'. Incorrect syntax near 'db'.
Can anyone explain why sp_executesql is behaving like that?
Microsoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
J2015
You can see examples at Microsoft's page for sp_executesql:
https://msdn.microsoft.com/en-us/library/ms188001.aspx
https://msdn.microsoft.com/en-us/library/ms188001.aspx
Correction to the definition. There shouldn't have been an extra apostrophe at the end:
'EXEC sp_executesql @sql, N'@xml xml', @xml = <value>
'EXEC sp_executesql @sql, N'@xml xml', @xml = <value>
ASKER
Here's the entire code:
I call the stored procedure with the following query:
USE [SQLXRAY_FE_EXTENSION]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_FetchXml] @List varchar(Max)
AS
DECLARE @xml xml, @sql nvarchar(255)
BEGIN
SET NOCOUNT ON
SET @xml=CAST(@List As XML)
SET @sql=' SELECT [SQLRONA].[dbo].[T_item].[
INNER JOIN [SQLRONA].[dbo].[T_item]
ON [SQLRONA].[dbo].[T_item].[
EXEC sp_executesql @sql, N'@xml xml', @xml
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_FetchXml] @List varchar(Max)
AS
DECLARE @xml xml, @sql nvarchar(255)
BEGIN
SET NOCOUNT ON
SET @xml=CAST(@List As XML)
SET @sql=' SELECT [SQLRONA].[dbo].[T_item].[
INNER JOIN [SQLRONA].[dbo].[T_item]
ON [SQLRONA].[dbo].[T_item].[
EXEC sp_executesql @sql, N'@xml xml', @xml
END
I call the stored procedure with the following query:
usp_FetchXml '<row sk="7805059" /> <row sk="0001982" /> <row sk="11635000" /> <row sk="11635001" />'
ASKER
I forgot to mention: I am using SQL Server 2008 R2.
This line:
EXEC sp_executesql @sql, N'@xml xml', @xml
should be:
EXEC sp_executesql @sql, N'@xml xml', @xml = @xml
To make it clearer:
EXEC sp_executesql @sql, N'@xmlData xml', @xmlData = @xml
EXEC sp_executesql @sql, N'@xml xml', @xml
should be:
EXEC sp_executesql @sql, N'@xml xml', @xml = @xml
To make it clearer:
EXEC sp_executesql @sql, N'@xmlData xml', @xmlData = @xml
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
By definition, it should be:
'EXEC sp_executesql @sql, N'@xml xml', @xml = <value>'
Where are you setting the passed in parameter equal to a value?