Read Xml String From DB in Classic Asp - odd return

Question

Hey folks,

So I've got our legacy app which is classic asp and I've got a table that looks like this:

CREATE TABLE ChangeRequests(
ChangeRequestsId int IDENTITY(1,1) NOT NULL,
XmlData nvarchar(max) NOT NULL)

Naturally "XmlData" has xml in it. The Xml string looks like this:

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfControlData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
  <ControlData>
    <Name>Email</Name>
    <Value>[email protected]</Value>
  </ControlData>
  <ControlData>
    <Name>PreferredLanguage</Name>
    <Value>English</Value>
  </ControlData>
</ArrayOfControlData>

So when I do one of these:

select XmlData from ChangeRequests

I'd expect to get the above string back. Here's the bit of code I use that I expect results from:

Set rs = Server.CreateObject("ADODB.recordset")
rs.Open "select XmlData from ChangeRequests", Conn

rs.MoveFirst
Response.Write rs("XmlData") & "<br />"

The result I get back is this cr4p:

[email protected]

Needless to say, I'd like the xml string back. I have a theory that the xml tag "" is a problem.

Just to cover any other assumptions made, I'm on MSSQL 2008 & IIS7.

Any ideas? Any help is appreciated.

Answer 1

I would guess that you are actually getting the XML properly.

What you are probably doing is outputting it directly to a page, expecting it to render directly.

You can HTMLEncode your XML in order for it to appear on the page:

Response.Write Server.HTMLEncode(rs("XmlData")) & "<br />"

Answer 2

Alternatively, you could do the following.

Response.ContentType = "text/xml"
Response.Write(rs("XmlData")