InfoPath Dev

 I have a form with two SQL Server data connections that SHOULD be identical, but I'm unable to COPYTABLE between them. One data connection was created up via a SOAP endpoint (SQL Server 2008R2). It appears that the data items are configured as elements. The second data connection was created using a SQL Server EXEC to the same stored procedure. All of the data items match up perfectly and both data connections work fine. However, this second data connection's data are set up as attributes. So, although the names are all the same under a repeating group, the COPYTABLE function doesn't see them as matching. (For the record, I'm trying to retool my forms to no longer use the ENDPOINTs hence the duplicate connections)

Is there a way to be able to do the data copy or be able to generate the second data connection to use elements instead of attributes for housing the data?

 

Thanks in advance for you insight. 

 Hi - are you familiar with XSLT? If so, using the transform command would be best (and if you attach a copy of your form to a reply under the options tab with info on what you are copying, I'd be happy to help you). Otherwise, you could use the insert & set values approach as described here - http://www.infopathdev.com/files/folders/qrules/entry58577.aspx

 I have attached the form. For this questions, the Datasource from the endpoint (the one with elements) is GetDivisions. The datasource from the SQL Server query is NE_GetDivisions (the one with attributes). I want to copy the data in the repeating group of  attributes in NE_GetDivisions to the repeating group of elements in GetDivisions.

 

Thanks for your consideration

 

For GetDivisions, I see both SqlRowSet1 and row1 are repeating? So is the desired output to have just be one instance of SqlRowSet1 with multiple instances of row1? Or for each row in NE_GetDivisions, do you need a SqlRowSet1 with a single row1? Let me know if that question doesn't make sense. :)

For some reason, the SOAP endpoint created the repeating group SQLROWSET1. I couldn't find any way of eliminating that. There would never be multiple datasets. The only one that is relevant is ROW1. That should correspond to the attributes in the other data connection.

Okay, thanks! I should be able to help you with a transform today & post your form back by the end of the day. :)

Ok - I've added a transform and a button to your form that executes the command so you can try it. Be sure you query the NE_GetDivisions data source prior to running the transform command - when I tested the transform I temporarily added XML data sources to a copy of the form to make sure I had the command right. Save the form locally, right click & select design, then you can preview.

 That seems to be working beautifully! Thank you so much. I can use that as a model as I have dozens of forms to apply this technique to. Due to the impending retirement of SQL Server 2008 R2 and SharePoint 2007 we have to quickly update our forms so they are not dependent on SOAP endpoints. I've got a technique that minimizes the translation process but was missing this last step. It looks like we'll be ok for now.

 

With Much Appreciation!!!!! 

Glad to hear that is working. We recommend the use of the Transform command for copying data whenever possible, simply because it is very efficient. Do you use Visual Studio at all? It can be helpful for testing your XSLT - Notepad ++, which is free, also has XML Plugins that can help you with that. For this transform, to test it, I created a dummy XML file that matched the schema of your source data connection, then debugged the XSLT in Visual Studio to verify my results. :)