Microsoft SQL Server forum - XML Problem in SQL Server 2000

Author

XML Problem in SQL Server 2000

Beetle

2005-05-11, 9:23 am

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? content_object_type_
id?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!

Simon Hayes

2005-05-11, 8:23 pm

"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? content_object_type_
id?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

Erland Sommarskog

2005-05-12, 7:23 am

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

Beetle

2005-05-24, 3:23 am

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

Erland Sommarskog

2005-05-24, 8:24 pm

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

Sponsored Links