Retrieving XML data by using FOR XML in SELECT statement

Forum related to MS SQL Server

Moderators: gto, cipto_kh, EgonHugeist

Post Reply
slimbyte
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 28.02.2009, 02:47

Retrieving XML data by using FOR XML in SELECT statement

Post by slimbyte »

Hi there,

I was wondering if is possible to retrieve XML data from SQL Server 2005 by using FOR XML in the SELECT statement.
I have successfully prepared the TZConnection using ado protocol and following connection string:

[font=Courier New]provider=SQLXMLOLEDB.3.0;data provider=SQLOLEDB;Integrated Security=SSPI;
Persist Security Info=False;Initial Catalog=sysmonitor;Data Source=WINXPDEV\SQLEXPRESS
[/font]


but if I execute the following statement in a TZQuery:

[font=Courier New]SELECT * FROM symServers ORDER BY symServers.[Name]
FOR XML RAW('Server'), ROOT('Servers')
[/font]


I get following error: SQLXMLOLEDB Provider command object can only execute to a stream

Anybody done this before with these components?

Thanks
slimbyte
Post Reply