Many times when using a Web Service I am looking to return just a set of values from a database to a front end form. One thing that I have been asked several times is how you would do this with InfoPath. The unfortunate problem as many have said is that currently InfoPath doesn’t support datasets. The answer to this is just to convert the return values to an XMLDocument.

For an example, I am going to create a Web Service that returns the companyname and contactname from the Northwind SQL Server sample database and connect this to an InfoPath front end.

To create this example, follow these steps:

1. Create a new Web Service that contains a single method that returns an XMLDataDocument.

<WebMethod()> Public Function GetRequestTypes() As System.xml.XmlDataDocument

'db connection

Dim sqlConn As SqlConnection

Dim sqlCmd As SqlCommand

Dim strConstring As String

Dim intUserID As Integer

strConstring = ConfigurationSettings.AppSettings("constring")

sqlConn = New SqlConnection(strConstring)

sqlConn.Open()

sqlCmd = New SqlCommand

With sqlCmd

.Connection = sqlConn

.CommandTimeout = 30

.CommandType = CommandType.Text

.CommandText = "Select companyname, contactname From Customers"

End With

Dim RequestDA As SqlDataAdapter = New SqlDataAdapter

RequestDA.SelectCommand = sqlCmd

Dim RequestDS As DataSet = New DataSet

RequestDA.Fill(RequestDS, "RequestType")

RequestDS.Namespace = "Http://localhost/RequestType"

Dim Info As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(RequestDS)

Return Info

sqlConn.Close()

End Function

End Class

Notice that instead of returning a dataset I have converted it to an XMLDataDocument.

2. Open InfoPath and design a new form using the Web Services Adaptor

3. As this Web Service only returns values make sure that you select Receive data.

4. Enter the path to the Web Service and complete the wizard. Once this is done the data source of the form will contain the values within the XMLDataDocument.

5. Drop the Request Type node onto the view and start the InfoPath form and click the “Run Query” button. This will contact and retrieve the data and then display the data.

For those that want to explore this example more completely, I have uploaded the sample files.