asp to xml abends

lipkinrm · August 20th, 2010, 04:08 PM

I am trying to write a classic asp routine to connect to a sqlexpress 2008 ( local server ) and extract an xml file from a very simple table .. the code you see has the schema part remarked out and I will deal with that later .. here is the code :

Code:

<%

response.ContentType = "text/xml"
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source = RICKLIPKIN-PC\SQLEXPRESS; Initial Catalog = VEHICLE; User Id = vehicleuser; Password=lipkinrm"

sql="select vnumber,license,agency from vehicles order by vnumber"
set rs=Conn.Execute(sql)

response.write("<?xml version='1.0' encoding='ISO-8859-1'?>")
'response.write("<?xml version='1.0' encoding='UTF-8'?>")

                                        
response.write("<NewDataSet>")
'response.write("<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">")
'    response.write("<xs:element name="NewDataSet" msdata:IsDataSet="true">" )
'      response.write("<xs:complexType>" )
'        response.write("<xs:choice maxOccurs="unbounded">" )
'          response.write("<xs:element name="Table">" )
'            response.write("<xs:complexType>" )
'              response.write("<xs:sequence>" )
'                response.write("<xs:element name="vnumber" type="xs:int" minOccurs="0" />" )
'                response.write("<xs:element name="license" type="xs:string" minOccurs="0" />" )
'                response.write("<xs:element name="type" type="xs:string" minOccurs="0" />" )
'              response.write("</xs:sequence>")
'            response.write("</xs:complexType>")
'          response.write("</xs:element>")
'        response.write("</xs:choice>")
'      response.write("</xs:complexType>")
'    response.write("</xs:element>")
'response.write("</xs:schema>")

While NOT rs.EOF
        response.write("<table>")
 
        sMyString = rs("vnumber")
        sMyString = Replace(sMyString, "/", " " )
        response.write("<vnumber>" & sMyString & "</vnumber>")

        sMyString = rs("license")
        sMyString = Replace(sMyString, "/", " " )
        response.write("<license>" & sMyString & "</license>")

        sMyString = rs("agency")
        sMyString = Replace(sMyString, "/", " " )
        sMyString = Replace(sMyString, "-", " " ) 

        response.write("<agency>" & sMyString & "</agency>")        

        response.write("</table>")

        rs.MoveNext()

Wend 


rs.Close()
conn.Close()

response write("</NewDataSet>")

%>

and about 95% of the way through the loop I get this error :

<vnumber>84401</vnumber>
<license>SG84401</license>
<agency>J04</agency>
</vehicles>
- <vehicles>
<vnumber>84491</vnumber>
<license>SG84491</license>
<agency>J04</agency>
</vehicles>
- <
The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
The following tags were not closed: NewDataSet. Error processing resource 'http://localhost/vehicle/datasetdata.asp'.

SPAN>vehicles>
<vnumber>84797</vnumber>

Any Ideas what is going on here .. it does not appear to be a data problem but the error occurs at the same place each time ..

Any advice would be helpful!!

Rick Lipkin
[email protected]

Old Pedant · August 20th, 2010, 05:26 PM

I think it *is* a data problem. You probably have some field that has a < or & or ' or some other character that is poison to XML.

There's an easy way to find out:

Remove this line:

Code:

response.ContentType = "text/xml"

and replace this line:

Code:

response.write("<?xml version='1.0' encoding='ISO-8859-1'?>")

with

Code:

response.write "<pre>" & vbNewLine

And now look at the same point in the output and see what is there.

Old Pedant · August 20th, 2010, 05:32 PM

FWIW, your code is going to execute more slowly as written--and be harder to debug the XML because of the lack of line breaks--than if you simply did:

Code:

While NOT rs.EOF
%>
    <table>
        <vnumber><%=clean(rs("vnumber"))%></vnumber>
        <license><%=clearn(rs("license"))%></license>
        <agency><%=clean(rs("agency"))%></agency>
    </table>
<%
    rs.MoveNext()
Wend
...

And then you could write a clean( ) function to remove those slashes (why? but up to you) and also "sanitize" the text for XML.

Maybe something like this:

Code:

Function clean( text )
    text = Replace( text, "/", " " )
    text = Replace( text, "&", "&amp;" )
    text = Replace( text, "<", "&lt;" )
    text = Replace( text, "'", "&apos;" )
    ... any others? ...
    clean = text
End Function

lipkinrm · August 20th, 2010, 06:08 PM

I made the suggestion you ask and I got the raw data horizontly across the screen .. I was able to verify that then entire 1457 records did indeed show up .. here ( hopefully is a screen shot of the data at the point where my xml breaks :

http://img440.imageshack.us/img440/6098/preec.jpg

I do not know why the code breaks .. I will try your suggestions above ..

Thanks
Rick Lipkin
[email protected]

Old Pedant · August 20th, 2010, 06:33 PM

You should click on the VIEW menu and then on the SOURCE menu item, so that you can see the XML tags at the point of the break! XML tags, when viewed *as* HTML, are simply invisible, so there's no way to see what's being generated. But do the VIEW==>>SOURCE and it should all be clearer.

And now you know why you should be putting line breaks into your XML! It makes it *MUCH* more readable when you are trying to debug!

If you code as I showed, *NOT* using Response.Write, the line breaks will be there.

lipkinrm · August 20th, 2010, 06:37 PM

Here is the code I inserted as per your suggestions and the xml abends at the same place :

Code:

<%

response.ContentType = "text/xml"
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source = RICKLIPKIN-PC\SQLEXPRESS; Initial Catalog = VEHICLE; User Id = vehicleuser; Password=lipkinrm"

sql="select vnumber,license,agency from vehicles order by vnumber"
set rs=Conn.Execute(sql)

'response.write("<?xml version='1.0' encoding='ISO-8859-1'?>")
'response.write("<?xml version='1.0' encoding='UTF-8'?>")
response.write("<?xml version='1.0' standalone='yes' ?>")
'response.write "<pre>" & vbNewLine

                                        
response.write("<NewDataSet>")
'response.write("<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">")
'    response.write("<xs:element name="NewDataSet" msdata:IsDataSet="true">" )
'      response.write("<xs:complexType>" )
'        response.write("<xs:choice maxOccurs="unbounded">" )
'          response.write("<xs:element name="Table">" )
'            response.write("<xs:complexType>" )
'              response.write("<xs:sequence>" )
'                response.write("<xs:element name="vnumber" type="xs:int" minOccurs="0" />" )
'                response.write("<xs:element name="license" type="xs:string" minOccurs="0" />" )
'                response.write("<xs:element name="type" type="xs:string" minOccurs="0" />" )
'              response.write("</xs:sequence>")
'            response.write("</xs:complexType>")
'          response.write("</xs:element>")
'        response.write("</xs:choice>")
'      response.write("</xs:complexType>")
'    response.write("</xs:element>")
'response.write("</xs:schema>")



While NOT rs.EOF
%>
    <table>
        <vnumber><%=clean(rs("vnumber"))%></vnumber>
        <license><%=clean(rs("license"))%></license>
        <agency><%=clean(rs("agency"))%></agency>
    </table>
<%
    rs.MoveNext()
Wend

rs.Close()
conn.Close()

'response write("</NewDataSet>")
%>

<%
Function clean( text )
    text = Replace( text, "/", " " )
    text = Replace( text, "&", "&amp;" )
    text = Replace( text, "<", "&lt;" )
    text = Replace( text, "'", "&apos;" )
    clean = text
End Function

%>

and here is the error :

Code:

</table>
- <table>
  <vnumber>83853</vnumber> 
  <license>SG83853</license> 
  <agency>J04</agency> 
  </table>
- <table>
  <vnumber>83854</vnumber> 
  <license>SG83854</license> 
  <agency>J04</agency> 
  </table>
- <table>
  <vnumber>84368</vnumber> 
  <license>SG84368</license> 
  <agency>J04</agency> 
  </table>
- <table>
  <vnumber>84401</vnumber> 
  <license>SG84401</license> 
  <agency>J04</agency> 
The XML page cannot be displayed 

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later. 


--------------------------------------------------------------------------------

The following tags were not closed: NewDataSet. Error processing resource 'http://localhost/vehicle/datasetdata.asp'. 


V>  </table>
- <table>
  <vnumber>84491</vnumber> 
  <license>SG84491</license> 
  <agency>J04</agency> 
  </table>
- <table>
  <vnumber>84797</vnumber>

lipkinrm · August 20th, 2010, 06:53 PM

As above I ran the code and the xml abended .. viewed source and all the elements were there ..

http://img295.imageshack.us/img295/4349/sourcer.jpg

Rick Lipkin
[email protected]

Old Pedant · August 20th, 2010, 06:55 PM

Really and truly weird.

It *LOOKS* like it is saying that the XML has a bogus

Code:

>V

in there.

But I see NO place that it should/could be coming from!

lipkinrm · August 20th, 2010, 07:07 PM

Got me stumped ... here is my web config if that matters ..

Code:

<?xml version="1.0" encoding="ISO-8859-1"?>
<configuration>
    <system.webServer>
        <directoryBrowse enabled="true" />
    </system.webServer>
</configuration>

I am running this under iis7 Windows 7 professional, SqlExpress 2008r2 and IE 8

Here is a screen shot of iis7 and the asp config :

http://img823.imageshack.us/img823/8075/aspe.jpg

Rick Lipkin
[email protected]

Old Pedant · August 20th, 2010, 07:36 PM

Maybe I do have an idea. I wonder if the IIS Response buffer is overflowing and causing junk to enter the stream??

You saw that max response size in the settings. It's high, but not enormous.

Maybe we should force a buffer flush from time to time?

How about this:

Code:

rows = 0
While NOT rs.EOF
%>
    <table>
        <vnumber><%=clean(rs("vnumber"))%></vnumber>
        <license><%=clearn(rs("license"))%></license>
        <agency><%=clean(rs("agency"))%></agency>
    </table>
<%
    rows = rows + 1
    If ( rows MOD 100 ) = 0 Then Response.Flush
    rs.MoveNext()
Wend
...