The QuerySharepoint web service allows you to retrieve data from a SharePoint list. It requires a <query> parameter, which can be built using the QueryBuilder form that comes with DBXL. QueryBuilder allows you to specify the columns you wish to retrieve.

In the screenshot above, you can see that I wish to retrieve three columns. I generate my query and proceed to create the data connections in my form. In InfoPath, I go to Tools > Data connections and create a receive-type connection. For the <query> parameter, I enter:

<query maxrows='2'><columns><column name='Category'/><column name='Unit_x0020_of_x0020_Measure'/><column name='ProductCode'/></columns></query>

Notice that the data source taskpane will show me the xml structure that was returned by this new data connection:

Where are my other two columns?! This issue was recently encountered by one of the users of DBXL (read it here).

If we look at the source SharePoint list, we’ll notice that the first two items in my list contain blank values in those columns.

Because of these blank values, the web service was unable to derive the correct schema.

The workaround is simple: increase the value of the maxrows parameter. Then the data connection “sees” that third row, and returns the columns correctly:

That’s a bit tedious, especially if you have a large list with many blank values. So the workaround becomes even simpler: remove the maxrows parameter!