Line/Paragraph Breaks in SQL Server/XML output

Hi again,
I was trying out a stored proc that outputs a huge XML file. It works fine
but my problem is the linebreaks it comes out with. What I did to test it
was to output it as text using Query Analyzer and then saved it as an XML
file with the headers and root elements. It kept on giving me errors. I
found out that it was because of paragraph breaks that split tags and such
thus causing the errors on the XML.
What I did to remedy the problem was to copy and paste to word then Searched
and Replaced for paragraph breaks and removed them. When I saved this to an
XML file, it worked fine. I tried doing a REPLACE on the resulting
textstream from an ASP page but it was giving me errors.
So my question is, how do I remove those linebreaks from the XML output so
that my ASP page is correctly rendered? Is there a setting I missed?
Thanks a bunch guys!
Jeeves
How exactly are you outputing the file from the Stored Proc? The basic
problem is that SQL Server 2000 doesn't have a native XML type, so it treats
the results of a FOR XML query as a string (this changes in SQL Server
2005). A better approach might be to use the SQLOLEDB or SQLXMLOLEDB
provider to retrieve the XML as a stream or DOMDocument in your ASP script.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Jeeves De Veyra" <me@.jeevester.com> wrote in message
news:e5GUZR6rEHA.2016@.TK2MSFTNGP12.phx.gbl...
Hi again,
I was trying out a stored proc that outputs a huge XML file. It works fine
but my problem is the linebreaks it comes out with. What I did to test it
was to output it as text using Query Analyzer and then saved it as an XML
file with the headers and root elements. It kept on giving me errors. I
found out that it was because of paragraph breaks that split tags and such
thus causing the errors on the XML.
What I did to remedy the problem was to copy and paste to word then Searched
and Replaced for paragraph breaks and removed them. When I saved this to an
XML file, it worked fine. I tried doing a REPLACE on the resulting
textstream from an ASP page but it was giving me errors.
So my question is, how do I remove those linebreaks from the XML output so
that my ASP page is correctly rendered? Is there a setting I missed?
Thanks a bunch guys!
Jeeves
|||Jeeves mentioned that the result was saved through the Query Analyzer.
The QA in SQL Server 2000 should not be used for that since it does not
support the XML stream. In SQL Server 2000, the XML stream is build on top
of the rowset streaming of TDS by sending a 4kB block as a row. The query
analyzer just shows the rows as it normally would.
The best ways to save your data in an ASP script is to use stream interface
of the OLEDB provider as Graeme points out. If you want to store it
manually, you can either write a VB Script or you can use the SQLXML ISAPI
component and then save it through your browser.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:%237OwhoEsEHA.376@.TK2MSFTNGP14.phx.gbl...
> How exactly are you outputing the file from the Stored Proc? The basic
> problem is that SQL Server 2000 doesn't have a native XML type, so it
> treats
> the results of a FOR XML query as a string (this changes in SQL Server
> 2005). A better approach might be to use the SQLOLEDB or SQLXMLOLEDB
> provider to retrieve the XML as a stream or DOMDocument in your ASP
> script.
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Jeeves De Veyra" <me@.jeevester.com> wrote in message
> news:e5GUZR6rEHA.2016@.TK2MSFTNGP12.phx.gbl...
> Hi again,
> I was trying out a stored proc that outputs a huge XML file. It works fine
> but my problem is the linebreaks it comes out with. What I did to test it
> was to output it as text using Query Analyzer and then saved it as an XML
> file with the headers and root elements. It kept on giving me errors. I
> found out that it was because of paragraph breaks that split tags and such
> thus causing the errors on the XML.
> What I did to remedy the problem was to copy and paste to word then
> Searched
> and Replaced for paragraph breaks and removed them. When I saved this to
> an
> XML file, it worked fine. I tried doing a REPLACE on the resulting
> textstream from an ASP page but it was giving me errors.
> So my question is, how do I remove those linebreaks from the XML output so
> that my ASP page is correctly rendered? Is there a setting I missed?
> Thanks a bunch guys!
> Jeeves
>
>
sql