SQLTeam.com | Weblogs | Forums

SQL Server 2014- Export Data without html formatting to .txt file

sql2014

#1

I need to export some Database data into a text file. My Query looks like this:

SELECT Category1, Category2, Category3, Category4
FROM dbo.tbl1
WHERE Category1 = 'JP-4'
AND Category2> 4;

This works fine to get the data, however there is some html formatting in the table entries such as
<p>,</p>,<br>,</br> etc. So ideally I need to remove those when exporting the data to the text file. I've tried to do it with a simple replace query but that didn't work.
I've also got an issue with line splits and would need to remove the (\n\r).
Any suggestions on how to do this would be really appreciated!

The Data format is something like this:

Category1: JP-4
Category2: 4
Category3:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p>  <p>amet, consectetur, adipisci velit</p>
Category4:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p>

#2

replace should work fine. show that code.


#3
SELECT REPLACE(REPLACE("PHOTOGRAPHS",'<p>',''),'</p>','') 
FROM dbo.khia_tbl
WHERE Category1= 'JP-4'
AND Category2> 4;

it works like this but the issue is that I've got 15 columns in total and that I need to do it for several different tags for each column so

<p></p>,<br></br>,

as well as "\n" and different spaces so that would be a lot and I thought there must be a better/more efficient way of doing it


#4

another way is to leverage the SQL Server XML machinery. Read the data in to an xml column/variable and use the values() method to extract the data.