Exporting XML file from SQL Server using FOR XML AUTO

Hello All,
I'm trying to export XML using osql or the sp_makewebtask and having
problems having IE 6.0 read the outputted format. The file is outputted
contains control line feeds in the middle of a row causing IE to throw up
errors. Is there a way to avoid these random control linefeeds in the file?
Any help is greatly appreciated.
Thanks in advance,
Frank
Hi Frank,
We have experienced this problem also and found out that there is actually a bug with FOR...XML statements when executed over ODBC. As you've discovered, it only returns 2083 characters per line.
Here is the link to the KB article:
http://support.microsoft.com/default...;en-us;Q275583
If you do a "Search By Author" on my username, you can find a list of some other posts I've made regarding this issue one of which discusses a workaround using OLE DB and DTS.
HTH,
Denise E. White
Technical Director
The Next Version Ltd. UK
www.thenextversion.com
www.denisewhite.co.uk
-- Frank DeLuccia wrote: --
Hello All,
I'm trying to export XML using osql or the sp_makewebtask and having
problems having IE 6.0 read the outputted format. The file is outputted
contains control line feeds in the middle of a row causing IE to throw up
errors. Is there a way to avoid these random control linefeeds in the file?
Any help is greatly appreciated.
Thanks in advance,
Frank
|||Hi...
My solutions... after 3 weeks....
PROCEDURE XXX
AS
SET NOCOUNT ON
CREATE TABLE ##Tmp_OTs
(
WoXML nvarchar(200)
)
INSERT INTO ##Tmp_OTs VALUES (HTMLFILE)
INSERT INTO ##Tmp_OTs VALUES ('<XML id="xmlWO" version="1.0"
encoding="ISO-8859-1">')
INSERT INTO ##Tmp_OTs VALUES ('<OTS>')
INSERT INTO ##Tmp_OTs
SELECT ( CREATE XML FORMAT BY RECORD)
'<OT>' +
'<A>' + FIELD1 + '</A>' +
'</OT>'
FROM TABLE
INSERT INTO ##Tmp_OTs VALUES ('</OTS>')
INSERT INTO ##Tmp_OTs VALUES ('</XML>')
EXEC sp_makewebtask
@.outputfile = 'OUTPUT FILE',
@.query = 'SELECT * FROM ##Tmp_OTs',
@.templatefile = 'TEPLATE FILE'
*************************************************
TEMPLATE FILE
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
I'm sorry my english not good.
Regards
Don Cata
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:DAEB7668-1A03-4156-A99D-531700166BC1@.microsoft.com...
> Hi Frank,
> We have experienced this problem also and found out that there is actually
a bug with FOR...XML statements when executed over ODBC. As you've
discovered, it only returns 2083 characters per line.
> Here is the link to the KB article:
> http://support.microsoft.com/default...;en-us;Q275583
> If you do a "Search By Author" on my username, you can find a list of some
other posts I've made regarding this issue one of which discusses a
workaround using OLE DB and DTS.
> HTH,
> Denise E. White
> Technical Director
> The Next Version Ltd. UK
> www.thenextversion.com
> www.denisewhite.co.uk
>
> -- Frank DeLuccia wrote: --
> Hello All,
> I'm trying to export XML using osql or the sp_makewebtask and
having
> problems having IE 6.0 read the outputted format. The file is
outputted
> contains control line feeds in the middle of a row causing IE to
throw up
> errors. Is there a way to avoid these random control linefeeds in
the file?
> Any help is greatly appreciated.
> Thanks in advance,
> Frank
>
>
|||Thanks for the help!!
"DonCata" <cavelardo@.hotmail.com> wrote in message
news:%23EfO7xZSEHA.644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi...
> My solutions... after 3 weeks....
> PROCEDURE XXX
> AS
> SET NOCOUNT ON
> CREATE TABLE ##Tmp_OTs
> (
> WoXML nvarchar(200)
> )
> INSERT INTO ##Tmp_OTs VALUES (HTMLFILE)
> INSERT INTO ##Tmp_OTs VALUES ('<XML id="xmlWO" version="1.0"
> encoding="ISO-8859-1">')
> INSERT INTO ##Tmp_OTs VALUES ('<OTS>')
> INSERT INTO ##Tmp_OTs
> SELECT ( CREATE XML FORMAT BY RECORD)
> '<OT>' +
> '<A>' + FIELD1 + '</A>' +
> '</OT>'
> FROM TABLE
> INSERT INTO ##Tmp_OTs VALUES ('</OTS>')
> INSERT INTO ##Tmp_OTs VALUES ('</XML>')
> EXEC sp_makewebtask
> @.outputfile = 'OUTPUT FILE',
> @.query = 'SELECT * FROM ##Tmp_OTs',
> @.templatefile = 'TEPLATE FILE'
>
> *************************************************
> TEMPLATE FILE
> <%begindetail%>
> <%insert_data_here%>
> <%enddetail%>
> I'm sorry my english not good.
> Regards
> Don Cata
> "mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
> news:DAEB7668-1A03-4156-A99D-531700166BC1@.microsoft.com...
actually[vbcol=seagreen]
> a bug with FOR...XML statements when executed over ODBC. As you've
> discovered, it only returns 2083 characters per line.
some
> other posts I've made regarding this issue one of which discusses a
> workaround using OLE DB and DTS.
> having
> outputted
> throw up
> the file?
>
|||Thanks for the help!!!
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:DAEB7668-1A03-4156-A99D-531700166BC1@.microsoft.com...
> Hi Frank,
> We have experienced this problem also and found out that there is actually
a bug with FOR...XML statements when executed over ODBC. As you've
discovered, it only returns 2083 characters per line.
> Here is the link to the KB article:
> http://support.microsoft.com/default...;en-us;Q275583
> If you do a "Search By Author" on my username, you can find a list of some
other posts I've made regarding this issue one of which discusses a
workaround using OLE DB and DTS.
> HTH,
> Denise E. White
> Technical Director
> The Next Version Ltd. UK
> www.thenextversion.com
> www.denisewhite.co.uk
>
> -- Frank DeLuccia wrote: --
> Hello All,
> I'm trying to export XML using osql or the sp_makewebtask and
having
> problems having IE 6.0 read the outputted format. The file is
outputted
> contains control line feeds in the middle of a row causing IE to
throw up
> errors. Is there a way to avoid these random control linefeeds in
the file?
> Any help is greatly appreciated.
> Thanks in advance,
> Frank
>
>
|||Sorry, this was not a bug, this was a design decision that you needed to
recompose the FOR XML results yourself over ODBC.
Yukon will do it for you.
Best regards
Michael
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:DAEB7668-1A03-4156-A99D-531700166BC1@.microsoft.com...
> Hi Frank,
> We have experienced this problem also and found out that there is actually
> a bug with FOR...XML statements when executed over ODBC. As you've
> discovered, it only returns 2083 characters per line.
> Here is the link to the KB article:
> http://support.microsoft.com/default...;en-us;Q275583
> If you do a "Search By Author" on my username, you can find a list of some
> other posts I've made regarding this issue one of which discusses a
> workaround using OLE DB and DTS.
> HTH,
> Denise E. White
> Technical Director
> The Next Version Ltd. UK
> www.thenextversion.com
> www.denisewhite.co.uk
>
> -- Frank DeLuccia wrote: --
> Hello All,
> I'm trying to export XML using osql or the sp_makewebtask and
> having
> problems having IE 6.0 read the outputted format. The file is
> outputted
> contains control line feeds in the middle of a row causing IE to throw
> up
> errors. Is there a way to avoid these random control linefeeds in the
> file?
> Any help is greatly appreciated.
> Thanks in advance,
> Frank
>
>