Re: Only getting data type:string when reading from a secondary data source (SQL Server 2005)

Patrick Halstead — Sun, 12 Sep 2010 23:48:00 GMT

Hi Jeff,

That's a limitation of InfoPath's schema inference logic. If you create your schema from a DB (not a best practice) it queries SQL for data type info. But, if you create your schema from XML, it has no clue what the data types are. You could load it as a secondary data source and copy the values over. Or, you could use a Web service like Qdabra's DBXL to solve your problem and this would provide a data-driven approach so future changes to the client or server schemas could be easily managed without an interruption in the submit process.

But, you're a dev and you want to do it yourself, so the options are:

Use a secondary data source and copy the values into your main data source - requires code or qRules
Use InfoPath's Import() object model method to bring the data in (and define an XSL/XSD to handle the mapping) - requires code
Create a receive data connection from a DB, but unsure how you could map to your end point
Create a Web Service on top of your SQL stored proc that has the correct typing (hard coded binding though will cause migration issues down the road)
Use an existing data-driven Web service (like Qdabra's DBXL) to do this - no code and will save you time, but you have to buy something
... etc. - I'm sure there's something I can't think of ...

Best of luck!

Only getting data type:string when reading from a secondary data source (SQL Server 2005)

DC-jeff — Fri, 10 Sep 2010 19:36:08 GMT

I'm using a SQL server stored procedure which generates XML to populate a repeating table. When infopath reads the XML, all data types are showing up as type string which is causing problems in my table with int, dates, etc. I'm unable to change the data type via the IP UI to override. How can this be set via the stored procedure so the XML caries the appropriate data type attributes or allow in to assign its own datatypes to the xml fields? SQL server stored procedure below.

TIA,

Jeff

---------------------------------------------------------

ALTER procedure [afpirs].[prcNewTestXMLRead] @inContractKey int AS BEGIN select 1 as tag ,null as parent ,null as [Contract!1!ContractKey] ,null as [AFEval!2!AFEval_ID] ,null as [AFEval!2!field2] ,null as [AFEval!2!field1] ,null as [AFEval!2!AFEval_EndDate] ,null as [AFEval!2!AFEval_SchedDate] ,null as [AFEval!2!AFEval_ApprovedAmt] ,null as [AFEval!2!AFEval_AvailableAmt] ,null as [AFEval!2!AFEVal_RatingCode] ,null as [AFEval!2!AFEval_DetailsText] ,null as [AFEval!2!AFEval_DFDetailsText] ,null as [AFEval!2!AFEval_CompleteFlag] ,null as [AFEval!2!AFEval_CompleteDate] ,null as [AFEval!2!AFEval_FileAttachment] ,null as [AFEval!2!AFEval_CreateDate] ,null as [AFEval!2!AFEval_CreateID] ,null as [AFEval!2!AFEval_UpdateDate] ,null as [AFEval!2!AFEval_UpdateID]from TestWeb.afpirs.bobtestregunion select 2 as tag ,1 as Parent,ContractKey,AFEval_ID,field2, field1, AFEval_EndDate, AFEval_SchedDate, AFEval_ApprovedAmt, AFEval_AvailableAmt, AFEVal_RatingCode, AFEval_DetailsText, AFEval_DFDetailsText, AFEval_CompleteFlag, AFEval_CompleteDate, AFEval_FileAttachment, AFEval_CreateDate, AFEval_CreateID, AFEval_UpdateDate, AFEval_UpdateIDfrom TestWeb.afpirs.bobtestregwhere ContractKey = @inContractKey --for XML explicit--for XML rawFOR XML AUTO, ELEMENTS XSINIL,ROOT('AFEvaluations') END

SQL Database Integration

Re: Only getting data type:string when reading from a secondary data source (SQL Server 2005)

Only getting data type:string when reading from a secondary data source (SQL Server 2005)