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_description>Node</object_type_description>...
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?o bject_type_description?content_object_type_id?cont ent_object_id?content_object_type_description?site _id?target_audience_name?target_audience_id?PageCo unt?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.googlegr oups.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_description>Node</object_type_description>...
> 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?o bject_type_description?content_object_type_id?cont ent_object_id?content_object_type_description?site _id?target_audience_name?target_audience_id?PageCo unt?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@yahoo.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_description>Node</object_type_description>...
> 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?o bject_type_description?con
tent_object_type_id?content_object_id?content_obje ct_type_description?site_i
d?target_audience_name?target_audience_id?PageCoun t?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/techinf...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:schemas-microsoft-com:xml-sql'>" & _
"xmlfetch_emp</sql:query></root>"

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection =
"Provider=SQLOLEDB;Server=basundi;Database=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@yahoo.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/techinf...2000/books.asp