XML Problem in SQL Server 2000

We are having a problem with XML in SQL Server 2000.
When we run a stored procedure that uses FOR XML Explicit from the
Query Analyzer, it returns the correct format :
<HIT_SUMMARY><rank>1</rank><hits>1</hits><object_type_id>2</object_type_id><
object_id>19</object_id><object_title>About
Us</object_title>< object_type_descript
ion>Node</ object_type_descript
ion>...
etc

But when we try and execute this from asp or VB it returns the
incorrect syntax:
?rank?hits?object_type_id?object_id?object_title? object_type_descript
ion?con
tent_object_type_id?content_object_id? content_object_type_
description?site_i
d? target_audience_name
?target_audience_id?PageCount?CurrentPage?PageSize?...
..etc

It used to work perfectly...is this something to do with collation or
service packs?

Thanks in advance!

"Beetle" < ed_hall_london@yahoo
.co.uk> wrote in message
news:1115822354.460148.230250@z14g2000cwz.googlegroups.com...
> We are having a problem with XML in SQL Server 2000.
> When we run a stored procedure that uses FOR XML Explicit from the
> Query Analyzer, it returns the correct format :
> <HIT_SUMMARY><rank>1</rank><hits>1</hits><object_type_id>2</object_type_id
><object_id>19</object_id><object_title>About
> Us</object_title>< object_type_descript
ion>Node</ object_type_descript
ion>..
.
> etc
>
> But when we try and execute this from asp or VB it returns the
> incorrect syntax:
> ?rank?hits?object_type_id?object_id?object_title? object_type_descript
ion?c
 ontent_object_type_i
d?content_object_id? content_object_type_
description?site
_id? target_audience_name
?target_audience_id?PageCount?CurrentPage?PageSize?.
....etc
>
> It used to work perfectly...is this something to do with collation or
> service packs?
>
> Thanks in advance!
>

I have no idea myself, but you might try posting in
microsoft.public.sqlserver.xml.

Simon

Beetle  (ed_hall_london@yaho
o.co.uk)  writes:
> We are having a problem with XML in SQL Server 2000.
> When we run a stored procedure that uses FOR XML Explicit from the
> Query Analyzer, it returns the correct format :
><HIT_SUMMARY><rank>1</rank><hits>1</hits><object_type_id>2</object_type_id>
<object_id>19</object_id><object_title> About

>
Us</object_title>< object_type_descript
ion>Node</ object_type_descript
ion>...
> etc
>
> But when we try and execute this from asp or VB it returns the
> incorrect syntax:
>
?rank?hits?object_type_id?object_id?object_title? object_type_descript
ion?con
tent_object_type_id?content_object_id? content_object_type_
description?site_i
d? target_audience_name
?target_audience_id?PageCount?CurrentPage?PageSize?...
...etc
>
> It used to work perfectly...is this something to do with collation or
> service packs?

If you are using the SQLOLEDB provider, you cannot receive FOR XML as a
recordset. Well, you can, but you get a binary blob instead. The recommended
way is to get it through a stream instead.

If you use the MSDASQL proivder, that is OLE DB over ODBC, you get the XML
as expected. However, usage of MSDASQL with SQLOLEDB is deprecated.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Hi there

Thanks for the responses.
We managed to "work around" this problem by using the .dialect property
of the command object.
Here is the code we used:

 ====================
 ====================
 ====================
===
Dim oCmd, sSQL
Dim str1
sSQL = "<root><sql:query
 xmlns:sql='urn:schem
as-microsoft-com:xml-sql'>" & _
"xmlfetch_emp</sql:query></root>"

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection =
" Provider=SQLOLEDB;Se
 rver=basundi;Databas
 e=sutton;UID=sa;PWD=
sa;"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

''' This assumes you're using IIS 5
''' In IIS4 the Response object doesn't implement the IStream
''' interface.

'   Create the output stream to stream the results into.
Set outStrm = CreateObject("ADODB.Stream")
outStrm.Open

oCmd.Properties("Output Stream") = outStrm
oCmd.Execute , , 1024
outStrm.Position = 0
'   Create temporary string.
Dim str
'   Assign the stream's output to the temp string to format.
str = outStrm.ReadText(-1)

Response.Write Server.HTMLEncode(str)

Set oCmd = Nothing

 ====================
=================

Hope you find this useful :)
Cheers

Beetle  (ed_hall_london@yaho
o.co.uk)  writes:
> Thanks for the responses.
> We managed to "work around" this problem by using the .dialect property
> of the command object.

Ah, interesting! Thanks for posting your findings!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp