I wanted to export the data from the views to the CSV using the BCP Command, with each field of data in double quotes as text qualifiers,
For that, I have specified that in the XML format as below:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" LENGTH="9" xsi:type="CharFixed" />
<FIELD ID="2" LENGTH="3" xsi:type="CharFixed" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Id_ein_empr" xsi:type="SQLNVARCHAR" LENGTH="9" />
<COLUMN SOURCE="2" NAME="Emp_Loc_nbr" xsi:type="SQLNVARCHAR" LENGTH="3" />
</ROW>
</BCPFORMAT>
And I am using this XML format in BCP query as:
For that, I have written the following query.
DECLARE
@DATACmd VARCHAR(500),
@FileLocation varchar(255),
@FileName varchar(255),
@StateCode varchar(2),
@v_Out_file_path varchar(255),
@v_Data_path varchar(255),
@header_format_path varchar(255),
@v_Header_path varchar(255),
@HeaderFileName varchar(255);
SET @FileLocation ='c:\csv\output'
SET @FileName = 'Araddis_'+FORMAT(Getdate(), 'MMddyyyy') + '.txt'
SET @v_Out_file_path = 'c:\csv\' + @StateCode + '\output\' + 'Output_' + @FileName
SET @v_Data_path = @FileLocation + '\' + @FileName
SELECT @DATACmd = 'bcp '
+ '"select * from Db_1.dbo.[Forms]"'
+ ' queryout ' + @FileLocation
+ '\' + @FileName
+ ' -f "C:\csv\Format_FL.xml" -T -S' + @@servername;
SELECT @DATACmd AS 'Command to execute';
EXECUTE master..xp_cmdshell @DATACmd;
If there were just three columns on the records I would have done like
SELECT id, age, QUOTENAME(name,'"') FROM DBName..vieter as mentioned: in the answer of this question.
But I can't select the field one by one because it will have at least 50-60 fields in the export, without specifying the individual column? Is there any way to add the quotes in the value, but ignore the null one?
