Returning recordset as xml via DomDocument in ASP results in error

Hi all,
I'm trying to return a recordset obtained from a sql server 2000 stored
procedure, as a text/XML content type via old asp.
The problem is the sql data contains foreign characters and when returning
the xml page to the IE6, the default xsl shows the error "invalid character"
for the foreign characters.
The code for the sp is the following:
Create Procedure webTest( @.nDummy Int )
As
Begin
Select Id = 1, Name = 'PARAGRAPH SYMBOL '
End
The code for the asp is:
<%@. Language=JavaScript %>
<%
Response.ContentType = "text/xml";
var cn=Server.CreateObject("ADODB.Connection");
var rs=Server.CreateObject("ADODB.Recordset");
var cmd=Server.CreateObject("ADODB.Command");
cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
Catalog=MATDESA;Data Source=sqlrespaldo01");
rs.CursorLocation = 3;
cmd.ActiveConnection = cn;
cmd.CommandType = adCmdStoredProc;
cmd.CommandText = "webTest";
var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
cmd.Parameters.Append(prm);
var rs = cmd.Execute();
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
rs.Close();
cn.Close();
%>
Looking at the return on IE6 it returns "invalid character" on the ""
symbol
But if I modify the code to save the recordset directly to the Response
stream, then it works. I mean if substituting the lines
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
by
rs.Save(Response, adPersistXML);
the browser shows ok the foreign symbols.
Where is the error on the first case? It looks like the created doc assumes
ascii character encoding or something alike...
Any hint is welcomed
Thanks in advance
Sammy"SammyBar" <sammybar@.gmail.com> wrote in message
news:e%2393NI9QHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm trying to return a recordset obtained from a sql server 2000 stored
> procedure, as a text/XML content type via old asp.
> The problem is the sql data contains foreign characters and when returning
> the xml page to the IE6, the default xsl shows the error "invalid
character"
> for the foreign characters.
> The code for the sp is the following:
> Create Procedure webTest( @.nDummy Int )
> As
> Begin
> Select Id = 1, Name = 'PARAGRAPH SYMBOL '
> End
> The code for the asp is:
> <%@. Language=JavaScript %>
> <%
> Response.ContentType = "text/xml";
> var cn=Server.CreateObject("ADODB.Connection");
> var rs=Server.CreateObject("ADODB.Recordset");
> var cmd=Server.CreateObject("ADODB.Command");
> cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
> Catalog=MATDESA;Data Source=sqlrespaldo01");
> rs.CursorLocation = 3;
> cmd.ActiveConnection = cn;
> cmd.CommandType = adCmdStoredProc;
> cmd.CommandText = "webTest";
> var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
> cmd.Parameters.Append(prm);
> var rs = cmd.Execute();
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> rs.Close();
> cn.Close();
> %>
> Looking at the return on IE6 it returns "invalid character" on the ""
> symbol
> But if I modify the code to save the recordset directly to the Response
> stream, then it works. I mean if substituting the lines
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> by
> rs.Save(Response, adPersistXML);
> the browser shows ok the foreign symbols.
> Where is the error on the first case? It looks like the created doc
assumes
> ascii character encoding or something alike...
>
All strings in script are unicode.
Hence doc.xml returns a unicode encoding of the XML document content.
Response.Write takes such a string and sends it to the client encoding it
using the the current Response.CodePage setting (Session.Codepage on
IIS5[.1])
The codepage defaults to the system codepage, typically in the West 1252.
Hence the character is encoded as a standard ANSI 1252 single byte
character.
However the receiving client XML DOM is expecting UTF-8 which is the default
encoding used by XML. Hence the character causes an error.
rs.Save(Response, adPersistXML)
OTH, will send the XML generated by the recordset in it's 'native' UTF-8
format directly to the client without being converted to another codepage.
Hence all is well.
As Martin has said this is the recommended approach.
BTW, personnally I prefer to tell the client explicitly it is receiving
UTF-8 using:-
Response.CharSet = "UTF-8"
Immediately after setting the content type.
Anthony.