[Visual Basic] vb return xml

How can return XML from a stored procedure using VB? (10762 Requests)

I've gotten quite a few requested for an example using a stored procedure instead of a template. So this example uses a regular stored procedure.

1. Create a new EXE project in VB.
2. Add references to MSXML 4.0 and ADO 2.6
3. Create the stored procedure listed below.
4. Copy the code below into your form's code. Be sure to change the connection string.

Stored Procedure

create proc employee_get
(
	@id int
)
as

	select 	FirstName, 
		LastName, 
		Title,
		Region
	from 	employees
	where	employeeid = @id
	for xml auto

go

VB Code

Dim oCmd As Command
Dim oPrm As Parameter
Dim oDom As IXMLDOMDocument2
   
Set oDom = New DOMDocument40
   
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=; " & _
    "User ID=; Password=; Database=Northwind"
       
oCmd.CommandText = "employee_get"
oCmd.CommandType = adCmdStoredProc
    
Set oPrm = New Parameter
oPrm.Name = "@id"
oPrm.Value = "1"
oPrm.Type = adInteger
oPrm.Size = 4
oCmd.Parameters.Append oPrm
        
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024

oDom.Save "c:\temp\results.xml"
    
Unload Me

Other Resources:
Returning XML in VB with a template

HOWTO: Retrieve XML Data by Using a SQL XML Query in a Visual Basic Client

HOWTO: Retrieve XML Data with a Template File from a Visual Basic Client