May 28, 2008 at 9:58 am
Hi guys!
I am pulling a load of articles from my database into an RSS feed using the 'XML Path' method. There's loads of info so I'm basically going to schedule a new xml file 'build' each day using SQL.
Once I have my constructed XML data, I'm putting it in a table in an 'xml' field then I can call it from the front end via a webpage.
My problem is, I keep getting invalid characters in the main bulk of my 'description' text and I was wondering if there is any way to 'strip' out non-valid chars???
Cheers!
May 30, 2008 at 3:21 am
What are you using to build the XML? Assuming you're using FOR XML - are you also using the TYPE directive?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 30, 2008 at 3:33 am
Hi,
Yes I'm using FOR XML and TYPE. I've actually solved this now. I wrote a function to replace some known problem characters and i just call that on each field that caused an issue. Also, I now store the xml in a table and apply the main rss channel tags over in the site after it's retreived. The other problem I noticed was the RSS encoding I was using. I changed it to use: encoding="ISO-8859-1" and all was fine.
The 'fn_RemoveInvalidXML' below simply takes the field and replaces a list of chars.
Here's the code - don't know if it could help anyone in the future:
declare @xmlTxt xml
set @xmlTxt = (SELECT TOP 100 PERCENT
dbo.fn_RemoveInvalidXML(item.txt_field) as title,
Left(dbo.fn_RemoveInvalidXML(item.txt_field2),250) as description,
LEFT(DATENAME(dw, dateAdd(day, 30, item.date)),3) + ', ' +
STUFF(CONVERT(nvarchar, dateAdd(day, 30, item.date),113),21,4,' GMT') as expiration_date,
LEFT(DATENAME(dw, item.date),3) + ', ' +
STUFF(CONVERT(nvarchar, item.date,113),21,4,' GMT') as publish_date
FROM my_table as item
FOR XML PATH('item'), TYPE)
insert into d_xmlTable(xml_feed)
values(@xmlTxt)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply