Exporting SQL data via BCP delimitted HL7

Question

I have the following HL7 message from SQL output stored in the $Message variable:

MSH|^~\&|System|System CRM|SYS|System CRM|20210222143236||ADT^A04|CRM001|P|2.4|||AL|NE
EVN||20210222143236
PID||9999999997^^^^NHSN|Ben^Smith^^^Mr||||||12A Cherry Avenue^Middleton^London^Greater London^E170RA^NSP^P|||
PD1|||^Amazing Surgery, Amazing Health Centre, London, E16 0RA|^DR. Fix
PV1|1|O

I'm trying to create the output to txt file for it:

DECLARE @Message NVARCHAR(MAX)

SET @Message = 'SELECT Message FROM #HL7'

DECLARE @sql VARCHAR(1000);
SELECT @sql = 'bcp ' + @Message + ' queryout "D:\HL7\Test.txt" -c -t|^ , -T -S'+ @@servername
PRINT @sql
EXEC master.dbo.xp_cmdshell @sql 

However, i seem to get the following error

' ' is not recognized as an internal or external command,
operable program or batch file.

When remove the |^ I get:

Copy direction must be either 'in', 'out' or 'format'.

Please can someone help I think the delimited is causing issues

Answer 1

Try below query:

SELECT @sql = 'bcp "' + @Message + '" queryout D:\HL7\Test.txt -c -t"|"^ , -T -S'+ @@servername

I have just exported data through below query:

DECLARE @Message NVARCHAR(MAX)

SET @Message = 'SELECT *FROM test.dbo.table_name'

DECLARE @sql VARCHAR(1000);
SELECT @sql = 'bcp "' + @Message + '" queryout D:\HL7\Test.txt -c -t"|"^ , -T -S'+ @@servername
PRINT @sql
EXEC master.dbo.xp_cmdshell @sql