XML Workshop XX - Generating an RSS 2.0 Feed with TSQL(SQL server 2000)

  • Comments posted to this topic are about the item XML Workshop XX - Generating an RSS 2.0 Feed with TSQL(SQL server 2000)


  • Excellent article..... Thanks.

  • Very useful ! Thanks a lot

  • Thanks for this great and useful article.

    I have 2 questions :

    1) In my result i have each 2034 character a feed line that make my xml file not XML compliant

    2) how to insert at the beginning of the XML result file this line


  • I think I did not understand the questions fully. could you elaborate?


  • With the line feed each 2034 character the XML result file can not be interpret well by other application because the line feed cut XML tag or data in element.

    The line that i want to insert is

    ?xml version="1.0" encoding="ISO-8859-1"?

  • You will have to do a string operation to do this. Look at the example given in the previous session: http://www.sqlservercentral.com/articles/XML/62661/


  • Thanks for your answer.

    With the encoding information, the line feed problem was disappeared.

    With UTF-8 I was an error : XML parsing: line 1, character 39, unable to switch the encoding

    I must use UTF-16. For information I use SQL server 2005.


  • Hello,

    I have this error :

    hexadecimal value 0x00, is an invalid character

    I sow your answer in Microsoft forum for a same problem:


    I used the same function : REPLACE([NewsSummary],CHAR(0),'')

    but that do not work.

    Do you have an idea ?


  • In our example, the REPLACE function is expected to remove the unwanted character (CHAR(0)). Try to run a SELECT query for the specific column and apply a REPLACE() operation and make sure those characters are removed from the query result.

    if that does not work, pls post some sample data. I will have a look at it and see If i can help you with this.




  • Thanks for your suggestion, the result of this test is incredible :

    With a SELECT the 0x00 character is not present and after use a FOR XML EXPLICIT the 0x00 character magically appears at the end of a CDATA element like this ...0x00]]><...

    I tried to force other column to CDATA but nothing appends.

    The column which cause this problem have as Data Type text, and store HTML code.

    have you already see that ?

  • could you post some script that reproduces the problem?


  • The SELECT :


    1 as Tag,

    null as Parent,



    LEFT(DATENAME(dw, [NewsDate]),3) + ', ' + STUFF(CONVERT(nvarchar,[NewsDate],113),21,4,' GMT')

    FROM [sd_News] WHERE [IDChannel] = '11'

    With the FOR XML :


    1 as Tag,

    null as Parent,

    [NewsTitle] as [item!1!title!ELEMENT],

    [NewsSummary] as [item!1!description!CDATA],

    LEFT(DATENAME(dw, [NewsDate]),3) + ', ' + STUFF(CONVERT(nvarchar,[NewsDate],113),21,4,' GMT') as [item!1!pubDate!ELEMENT]

    FROM [sd_News] WHERE [IDChannel] = '11'


    After some tests, it seems that the problem occurs when the HTML data store in the column NewsSummary is very important.


  • Hi,

    It does not really help because I have no way to run it and see the problem. Meanwhile, my only guess is that something is going wrong with your REPLACE() function. You should be able to remove a character by using the REPLACE() function correctly.


Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply