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

Last post 09-12-2010 04:48 PM by Patrick Halstead. 1 replies.

Page 1 of 1 (2 items)
	Sort Posts: Previous Next

09-10-2010 12:36 PM

DC-jeff
Joined on 09-09-2010
Posts 39

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

Reply Contact

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

Filed under: data connections, query data through stored proc, XML file, sql server 2005, 2007, SQL Server, secondary data source

09-12-2010 04:48 PM In reply to

Patrick Halstead
Joined on 08-08-2006
Posts 5,112

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

Mark as Not Answer Mark as Answer ...

Reply Contact

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!

Patrick Halstead
Project Manager at Qdabra

Page 1 of 1 (2 items)

InfoPath Dev