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

  • jacob sebastian

    SSChampion

    Points: 11812

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

    .

  • Anipaul

    SSC-Insane

    Points: 24681

    Excellent article..... Thanks.

  • Damien Joly

    Valued Member

    Points: 71

    Very useful ! Thanks a lot

  • Le Gloanec

    SSC Journeyman

    Points: 92

    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

    Regards

  • jacob sebastian

    SSChampion

    Points: 11812

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

    .

  • Le Gloanec

    SSC Journeyman

    Points: 92

    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"?

  • jacob sebastian

    SSChampion

    Points: 11812

    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/

    .

  • Le Gloanec

    SSC Journeyman

    Points: 92

    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.

    Regards

  • Le Gloanec

    SSC Journeyman

    Points: 92

    Hello,

    I have this error :

    hexadecimal value 0x00, is an invalid character

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

    replace(description,CHAR(25),'')

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

    but that do not work.

    Do you have an idea ?

    Regards

  • jacob sebastian

    SSChampion

    Points: 11812

    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.

    regards

    Jacob

    .

  • Le Gloanec

    SSC Journeyman

    Points: 92

    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 ?

  • jacob sebastian

    SSChampion

    Points: 11812

    could you post some script that reproduces the problem?

    .

  • Le Gloanec

    SSC Journeyman

    Points: 92

    The SELECT :

    SELECT

    1 as Tag,

    null as Parent,

    [NewsTitle],

    [NewsSummary],

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

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

    With the FOR XML :

    SELECT

    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'

    FOR XML EXPLICIT

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

    Regards.

  • jacob sebastian

    SSChampion

    Points: 11812

    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 14 (of 14 total)

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