-1

I have a table with 6 columns and need to convert that output to .json format and send output to .json format to a folder.

I'm testing only with one column. When I execute the select statement I get the correct output;

select'[' +STUFF((select ',{'+ '"LocationID":"' +LocationID++'"}'FROM ##TABLE FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,1,'') +']';

But when I add this select to bcp I get an error copy direction must be either 'in','out' or 'format'

DECLARE @cmd varchar(8000)
SET @cmd = 'bcp "select ''['' +STUFF((select '',{''+ ''"LocationID":"'' +LocationID+''"}''FROM ##TABLE FOR XML PATH(''),TYPE).value(''.'',''varchar(MAX)''),1,1,'') +'']''" queryout T:\filename.json -c -t, -T'
EXEC master..xp_cmdshell @cmd

Tried different things but didn't work. Appreciate your help.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Caterine Andrea Reyes Malaver
  • 1
  • 1

1 Answers1

0

Your crafted SQL command, nestled inside your created bcp command has a bug here:

FOR XML PATH('')

You seem to be already familiar with the need to use the "double single-quote" to get a single quote in a string, but you just missed a location to do this at. Your code should be:

FOR XML PATH('''')

NOTE: you should avoid using xp_cmdshell. It introduces severe security risks. xp_cmdshell should be disabled.

jamie
  • 745
  • 4
  • 11