Download an XML file from stored procedure output (1 Viewer)

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

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

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