Download an XML file from stored procedure output

[dealwi8me]

Hello,
I need to have a function that downloads an XML file from a stored procedure and saves it to the disk. Is this possible with MS Access VBA?

Here's my test sample in SQL Server:

CREATE TABLE dbo.TestXML(
ID int NOT NULL,
PID]  int NULL,
Code int NULL,
Col1 int NULL,
Col2 int NULL,
Col3 decimal(6, 2) NULL,
Col4 decimal(6, 2) NULL,
Col5 int NULL,
CONSTRAINT PK_TestXML PRIMARY KEY CLUSTERED (ID ASC)
)

GO

INSERT dbo.TestXML (ID, PID, Code, Col1, Col2, Col3, Col4, Col5) VALUES (1, 1000, 10, 1, 2, CAST(0.20 AS Decimal(6, 2)), CAST(0.10 AS Decimal(6, 2)), 1)
INSERT dbo.TestXML (ID, PID, Code, Col1, Col2, Col3, Col4, Col5) VALUES (2, 1000, 20, NULL, 1, CAST(1.00 AS Decimal(6, 2)), CAST(1.00 AS Decimal(6, 2)), 1)
INSERT dbo.TestXML (ID, PID, Code, Col1, Col2, Col3, Col4, Col5) VALUES (3, 1000, 30, NULL, NULL, CAST(2.00 AS Decimal(6, 2)), CAST(2.00 AS Decimal(6, 2)), 5)

GO

Sproc

CREATE PROCEDURE returnXML 
(@ID int,
 @xmlOut XML OUTPUT)

AS
BEGIN

SET NOCOUNT ON;

SELECT @xmlOut=(SELECT PID [p/@v],(
    SELECT Code as [code/@c], Col1 AS [code/val]
    FROM [dbo].[TestXML]
    WHERE ID=@ID
    FOR XML PATH(''),TYPE)
  FROM [dbo].[TestXML]
  WHERE ID=@ID
  FOR XML PATH('u'),TYPE)

END
GO

VBA (what I tried...)

Function getXML(sproc As String, id As Integer) As Object
Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim cnnStr As String
    Dim Rs As New ADODB.Recordset
    Dim StrSproc As String

    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command
    Set Rs = New ADODB.Recordset


    cnnStr = getConnString()

    With cnn
        .CommandTimeout = 900
        .ConnectionString = cnnStr
        .Open
    End With
    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = sproc
        .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, id)
        .Parameters.Append .CreateParameter("@xmlOut", adLongVarChar, adParamOutput)

    End With
    With Rs
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmd
    End With
    Set rst = cmd.Execute(, , adCmdStoredProc)
    getXML = cmd.Parameters("@xmlOut").Value

End Function

Thank you in advance.

 

[Minty]

Did you have a read of this thread https://www.access-programmers.co.uk/forums/showthread.php?t=295789&highlight=json as I suggested on stackoverflow ?

 

[dealwi8me]

Yes I did, but I can't see how it can help me.
What I need is to get an xml variable that a stored procedure outputs and save it to disk through a function.
I'm sorry I'm not an expert so it's more difucult for me, if you could be more specific about the example I would appreciated it.