Jeff Moden (10/7/2015)

---

In that case, especially since it's a "one off" task, I have to agree with Mister Magoo above in saying it's not worth trying to do this using any form of XML manipulation. It's also not worth trying to automate the export.

Rather, do the following from SSMS...

1. Borrowing heavily on Magoo's code, load the following code into SSMS but don't run it, yet. Of course, you'll need to change the column names and the table name but this code will run with Magoo's test data that he posted.

--===== Suppress the auto-display of rowcounts

SET NOCOUNT ON

;

--===== Create the output for the file exactly as they asked for it

SELECT REPLACE(REPLACE(REPLACE('

<Row>

<Cell ss:StyleID="s22">

<Data ss:Type="String">[Column A]</Data>

</Cell>

<Cell ss:StyleID="s22">

<Data ss:Type="String">[Column B]</Data>

</Cell>

<Cell ss:StyleID="s22">

<Data ss:Type="String">[Column C]</Data>

</Cell>

</Row>'

,'[Column A]',ISNULL([Column A],''))

,'[Column B]',ISNULL([Column B],''))

,'[Column C]',ISNULL([Column C],''))

FROM #Temp

;

2. Change the output type to "Results to File".

3. Run the query.

4. It'll ask you where you want to store the file and what you want to name it. Change the "Save as Type" to "All Files(*.*)", select the directory where you want to store the file, type the name.extension you want for the "File name:", and then click on the "Save" button.

5. Open the file using Notepad or your favorite basic text editor and delete the dashed line.

6. Save the file and close Notepad.