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].[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?

Shaun Kline

The format for sp_executesql does not look correct.

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?

Shaun Kline

You can see examples at Microsoft's page for sp_executesql:
https://msdn.microsoft.com/en-us/library/ms188001.aspx

Shaun Kline

Correction to the definition. There shouldn't have been an extra apostrophe at the end:
'EXEC sp_executesql @sql, N'@xml xml', @xml = <value>

J2015

ASKER

Here's the entire code:

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].[CodeRona] 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)'')'
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" />'

J2015

ASKER

I forgot to mention: I am using SQL Server 2008 R2.

Shaun Kline

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

ASKER CERTIFIED SOLUTION

J2015

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial