title: Transform JSON column to string
body: <p>I have a script that selects data into an XML table with a single column and then converts that into a string:</p>
  
  <pre><code>(
      SELECT N'DECLARE @' + REPLACE( COLUMN_NAME, ' ', '_' ) + @CrLf
      FROM @TABLE
      FOR XML PATH(''), TYPE
  ).value( '.', 'nvarchar(max)' ) + @CrLf + 
  </code></pre>
  
  <p>I'm trying to do the same thing with JSON but I just can't seem to figure out the last part.  I'm able to create a table with a single column but no matter what I try, I can't seem to get it to convert to a string:</p>
  
  <pre><code>(
      SELECT ( N'DECLARE @' + REPLACE( COLUMN_NAME, ' ', '_' ) + @CrLf ) AS MyString
      FROM @TABLE
      FOR JSON PATH
  )
  </code></pre>
  
  <p>Here is a testable sample (which includes the above):</p>
  
  <pre><code>DECLARE @CrLf       nchar(2) = CHAR(13) + CHAR(10);
  DECLARE @TABLE      TABLE ( [COLUMN_NAME] sysname );
  
  INSERT INTO @TABLE VALUES 
  ( 'COL1' ),
  ( 'COL2' ),
  ( 'COL3' ),
  ( 'COL4' ),
  ( 'COL5' );
  
  DECLARE @Script nvarchar(max) =
  (
      SELECT N'DECLARE @' + REPLACE( COLUMN_NAME, ' ', '_' ) + @CrLf
      FROM @TABLE
      FOR XML PATH(''), TYPE
  ).value( '.', 'nvarchar(max)' ) + @CrLf + 
  (
      SELECT ( N'DECLARE @' + REPLACE( COLUMN_NAME, ' ', '_' ) + @CrLf ) AS MyString
      FROM @TABLE
      FOR JSON PATH
  );
  
  SELECT @Script;
  </code></pre>
  
  <p>It outputs the following:</p>
  
  <pre><code>XML Transform:
  DECLARE @COL1
  DECLARE @COL2
  DECLARE @COL3
  DECLARE @COL4
  DECLARE @COL5
  
  JSON Transform:
  [{"MyString":"DECLARE @COL1\r\n"},{"MyString":"DECLARE @COL2\r\n"},{"MyString":"DECLARE @COL3\r\n"},{"MyString":"DECLARE @COL4\r\n"},{"MyString":"DECLARE @COL5\r\n"}]
  </code></pre>
  
  <p>So in closing, I'm trying to get the JSON Transform to look the same as the XML Transform does.</p>
  
  <p>Much appreciated.</p>