Must declare the scalar variable bcp Error

Question

alter PROC XML
AS

DECLARE @myXml xml
Declare @SQL nvarchar(2000)

SET @myXml = 'SELECT TOP 1000 *  FROM [xyz].[dbo].[yzx] FOR XML AUTO'
SET @SQL= 'bcp "exec @myXml" QUERYOUT C:\sample.xml -w -r -t -S www -T' 
EXEC Master..xp_CmdShell @SQL

When I run the above code I am getting error as below

SQLState = 37000, NativeError = 137
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Must declare the scalar variable "@myXml".
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unable to resolve column level collations

Even though I have declared the variable why am I getting the above error

Answer 1

You are using dynamic sql you need to pass the string value for execution, i think you also need to cast a xml type to varchar maybe this will help:

CREATE PROCEDURE dbo.Test1    
AS
    SET NOCOUNT ON;
    SELECT TOP 1000 *  FROM [xyz].[dbo].[yzx] FOR XML AUTO
GO

CREATE PROC XML
AS
DECLARE @myXml xml
Declare @SQL nvarchar(2000)

SET @myXml = 'dbo.Test1'
SET @SQL= 'bcp "exec ' + CAST(@myXml AS VARCHAR(max)) + '" QUERYOUT C:\sample.xml -w -r -t -S www -T' 
EXEC Master..xp_CmdShell @SQL