SQL Server 2005 Express Service Pack 2 w/Advanced Services

Windows 2003 R2

I recently migrated a database from MSDE 2000 to MSSQL 2005 that is used for a tourist website that can be displayed in one of three languages. A stored procedure is executed in ASP with a number that represents the language to use

Dim xml: xml = "<?xml version=""1.0"" encoding=""UTF-8""?>"

    response.write "<root>"
    set conn = server.createobject("ADODB.connection")
    conn.open application("bd")

    sqlTrad="EXEC psu_sitFicheTraduction @idLangue=1"               
    set rsTrad = Server.CreateObject("ADODB.Recordset")
    rsTrad.Open sqlTrad, conn, 1,1
    do while not rsTrad.eof 
        texte = rsTrad(0)
        response.write texte

The stored procedure EXEC psu_sitFicheTraduction runs the following SELECT query:

SELECT  1 AS Tag, NULL AS Parent,
        t.PK_txt AS [dicItem!1!ID],
        t.libelle_long_FR AS [dicItem!1!value],
        @idLangue as [dicItem!1!Langue]
FROM        txt_traduction t

For some reason, on the new server, the XML is displayed incorrectly:

This page contains the following errors:

error on line 1 at column 51: PCDATA invalid Char value 1 Below is a rendering of the page up to the first error.

This is the source of the page:


When I run the stored procedure in SSMS, I don't get any errors although I have noticed that in Query Analyzer on the MSDE 2000 server I get 23 rows, where as in SQL Server 2005 SSMS I only get one big row.

This is the first time I've seen FOR XML EXPLICIT used so I don't have any experience with it.

  1. Why is MSDE sending back 23 rows and SSMS only 1 big one
  2. Any thing I need to configure in IIS to correctly display XML output coming from SQL Server ?

Any other suggestions or assistance would be appreciated.

  • That query should return one row. Not sure what MSDE is doing. Does it return well-formed XML? I think there may be an issue with character encoding here, too. – Jon Seigel Jan 9 '13 at 19:17
  • In SSMS, the XML looks well formed, its in IIS that I get all those Asian characters above. – Craig Efrein Jan 10 '13 at 9:54
  • Okay, the issue is a character encoding issue then. XML is always 2 bytes per character, but if the application is expecting 1 byte per character, you get garbage output like that. – Jon Seigel Jan 10 '13 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.