Returning XML data from SQL Server Stored Procedure

[Tony Starr]

I have a Stored Proccedure in my SQL Server Database that queries the
database and returnsa hierarchical XML string.

How can I call this stored procedure from MS Access so that I can retrieve
this XML string and write it out to a file.

My Code looks like this

Sub test081207_1()
Dim x As String
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={SQL Native
Client};Server=TEMPEST5;Database=XelonReleases;Trusted_Connection=yes"
conn.Open

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandText = "usp_FetchTestXML"
cmd.CommandType = adCmdStoredProc
x = cmd.Execute
Debug.Print x
End Sub

My code falls over at
x = cmd.Execute
with a 'Type Mismatch" error


This is my stored procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Alter PROCEDURE [dbo].[usp_FetchTestXML]
AS
BEGIN

select [GUID] as "@LabelDigitalGUID", 'insert' as DeliveryType, [LabelName]
as LabelName, [CatNumber] as TitleCatalogueNumber , [ReleaseTitle] as
TitleName ,[ArtistName] as TitleArtistName , convert(Char,[UPCBarcode]) as
TitleUPC,[PhysicallyReleased] as TitlePhysicallyReleased ,
dbo.udf_GetJunoGenre('Juno',[AlbumGenre],1) as TitleFirstGenreId,
[TitleType],
(
select
(
select Restriction as "@Restriction", Code "*"
from XelonReleaseTerritory where XelonReleaseTerritory.ReleaseID
= XR2.ReleaseID for xml path ('CountryCode'), type
)
from XelonReleases as XR2 where XR2.ReleaseID = XelonReleases.ReleaseID
for xml path( 'TitleTerritory'), type
)
from XelonReleases inner join LabelGuids on LabelGuids.LabelID =
XelonReleases.LabelID inner join labels on XelonReleases.LabelID =
Labels.LabelID inner Join Artists on XelonReleases.ReleaseArtistID =
Artists.ArtistID
where LabelGuids.DSPid = 1 for xml path('release') , type

END

TIA
Tony

PS. I apologize fro the cross posting. I was not sure which newsgroup was
the most appropriate.