Parse SQL Server Data

I used SSIS and [SharePointListAdapters][1] [1]: http://sqlsrvintegrationsrv.codeplex.com/releases to import data from my SharePoint 2010 list and put it into SQL Server 2008 table. TThere are roughly 500 rows of data. Now the challenge is to parse data appropriately. I have a couple of columns that have html tags around them.

For e.g., Column Project Desc has data like Project Desc

<div class="ExternalClass914BB5DA5CB142EB854C739EAACEE3BB">
    <div>Import Data from SharePoint list to Database.
    </div>
</div>

​x
 
<div class="ExternalClass914BB5DA5CB142EB854C739EAACEE3BB">    <div>Import Data from SharePoint list to Database.    </div></div>​

On googling, I found the functio below. It works as expected. My questions is how do I edit this function to take the column name called Project Desc as a parameter so that all of the data in that particular column will have html tags discarded.

create function dbo.StripHTML( @text varchar(max) ) returns varchar(max) as 
begin 
    declare @textXML xml 
    declare @result varchar(max) 
    set @textXML = @text; 
    with doc(contents) as 
    ( 
        select chunks.chunk.query('.') from @textXML.nodes('/') as chunks(chunk) 
    ) 
    select @result = contents.value('.', 'varchar(max)') from doc 
    return @result 
end 
go 
select dbo.StripHTML('<div class="ExternalClassB1D90504EAFF42BE8A8056E686F4E195"> <div>Import Data from SharePoint list to Database.</div></div>')

 
create function dbo.StripHTML( @text varchar(max) ) returns varchar(max) as begin     declare @textXML xml     declare @result varchar(max)     set @textXML = @text;     with doc(contents) as     (         select chunks.chunk.query('.') from @textXML.nodes('/') as chunks(chunk)     )     select @result = contents.value('.', 'varchar(max)') from doc     return @result end go select dbo.StripHTML('<div class="ExternalClassB1D90504EAFF42BE8A8056E686F4E195"> <div>Import Data from SharePoint list to Database.</div></div>') ​

Answer

Once you’ve created the function, you just use it as if it was a normal field in a table, and put the source table and field in the place that the parameter goes…

SELECT
  dbo.StripHTML( yourTable.yourColumn ) AS yourResults
FROM
  yourTable

 
SELECT  dbo.StripHTML( yourTable.yourColumn ) AS yourResultsFROM  yourTable​

(You don’t edit the function, you just supply it with data from your table.)

Advertisement

Answer