InfoPath Dev

I have 2 web service datasources that retrieve data from a SQL DB and bound to their respective repeating tables in the form. I need to nest one of the repeating tables within the other repeating table for the purpose of showing all the data of datasource 2 that corresponds to each row received form datasource 1.
So to make it easy let me make an example that is easy to get (my project specific example would confuse more than help)

lets pretend we have a data source that gets Car manufacturers and car models. the first data source would be a web service that gets car manufacturers. the second datasource, would receive a car manufacturer ID code and look up all the models. So the EXPECTED result should look like this...

 
Name=Toyota |  idcode=1

          Name=Corolla | idcode=10

          Name=Camry | idcode=20

Name=Honda | idcode = 2
          Name=Prelude | idcode = 30

         name=Civic | idcode=40

Ok so what I did was drag the columns of one repeating table inside another repeating table. then the value of one of the fields returned by the first data source, is passed into the query field of the second data source which retrieves its values with that id key.

Here's the problem (and i have tried doing this programmatically as well but no dice):

It seems that whats is happening is, the inner model types repeating table is the SAME instance for EACH row of manufacturer. WHen I debug I see its calling the data source result for each row ok, I can see it loop with Corolla and Camry only. Then next iteration , its Prelude and Civic.

BUT, when the data is populated inside the Models repeating table, it overwrites what i had in there before and starts over and that affects EVERY model table result. So, even though the repeating table is being looped for EACH manufacturer row, its a cloned instance.

SO in simpler terms,the ACTUAL RESULT im getting is :

Name=Toyota |  idcode=1

        Name=Prelude | idcode = 30
         name=Civic | idcode=40

Name=Honda | idcode = 2

         Name=Prelude | idcode = 30
         name=Civic | idcode=40

 I hope this makes sense. So is it possible to have a unique repeating table within another repeating table without treating each inner table as a duplicate of others nested in diff rows of the outer table?

please help! 

1. Does your model table have any field that specifies the manufacturer so that you can filter?

2. Can you, instead of doing 2 SQL queries, just do a single query that returns all the correct data already in the format it needs to be (using the SHAPE command)?

Greg,

thank you very much for replying. To answer the questions:

1. Yeah filter would be an obvious choice but this is for a browser-enabled form thats going to go  to Sharepoint so unfortunately I cannot use filtering.

2. I could easily do one query to receive all the information. However each row returned would include the manufacturer and the model. So i would be forced to display the manufacturer every single time i display a model as opposed to listing the manufacturer once, then all its models underneath it. Or am I mistaken?

EDIT: I just looked up SHAPE and didnt realize such a thing existed. (I come from a Java/Oracle background). This will allow me to display data in a hierarchal fashion I presume?

Will I be able to maintain the same output as a Repeating Table? Thank you!
 

SHAPE is very powerful and is your best choice in a situation like this. It will let you create a hierarchical structure that you can use to display your data in the format you desire.

For example you might return a structure such as:

<Make name="Toyota" idcode="1">
    <Model name="Corolla" idcode="10"/>
    <Model name="Camry" idcode="20"/>
</Make>
<Make name="Honda" idcode="2">
    <Model name="Prelude" idcode="30"/>
    <Model name="Civic" idcode="40"/>
</Make>

Sorry to keep asking on this but: 

I cant figure out how to create a Shape that will work with what I need for Infopath. Let me paste my actual scenario, not this car made up one for accurate results:

This is still a simple example. Here, CLIN is just like our Make from the pervious example. SLIN is just like our Model. CLIN has a one to many relationship to the child SLIN table. CLINID is the foreign key in the SLIN table linking to the CLIN table.

My XML looks like this

 as you can see, it lists all the CLINS first with their references to the rsSLIN result. Then those SLINS are listed. So what happens is, I am still not able to nest these in Infopath.One Repeating Table applies to the CLIN row results. Another Repeating Table applies to the rsSLIN results. which leads me to the same impasse as before.i need to somehow see the children embedded inside each parent like you have in your example. Yet I dont see a way of doing this with shape even. What am i missing?

My query statement is as follows:

   SQLStatement = " SHAPE { Select CLINID, CLINNAME, CLINNUM, CLINTYPE FROM ACN_TBL_CLIN where clingroupid=5 } " +
                       " APPEND ( {Select SLINID, CLINID, SLINName, SLINNUM, SLINTYPE FROM ACN_TBL_SLIN} AS rsSLIN " +
                       " RELATE CLINID TO CLINID ) ";
 

ive been able to create a hierarchical structure use the FOR XML PATH in my query.  the problem is it doesnt return an XSD. it returns a reference to an XML file. how would i use this in Infopath?

figured it out. for anybody interested, i put my SQL statement in a Stored Procedure, then used an XMLReader to get the output
.

Great! Glad you got it working.

I was going to suggest the following:

1. Design an new blank InfoPath form template from your SQL source.
2. Add your tables and child tables as needed. It should create a nice hierarchical structure for you.
3. Modify your main data source and look at the SQL that InfoPath created... it will give you the exact SHAPE statement you need.