TD:
When you run the SQL, you should see something like this in the results:

state_code                   city
AK                             Anchorage
AK                             Cantwell
AK                             Cooper Landing

Your XML is reflecting the data in each row.  Since you want the data in a
more hierarchical format, you'll need to use Data Shaping.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;189657

In Access:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;185425

extracting data:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q191/7/44.ASP&NoWebContent=1


I've used this technique to pull data from an Oracle database via ADO, and
also inside Access (97 and 2000) to create hierarchal recordsets.

HTH!
Dave


"TD Matthews" <trevor_d_matthews@h...> wrote in message
news:a14bc00c.0407010909.4d4527cc@p......
> I have a simple question (I think). I have 2 tables - states and
> employees, and I want a breakdown of cities in those states in this
> form. With the city as a child of the state - I'm new to XML so please
> excuse it's innacuracy as I know it's not correct.
>
> <StateCity>
>   <state_code>AK
>     <city>Anchorage</city>
>     <city>Cantwell</city>
>     <city>Cooper Landing</city>
>   </state_code>
> </StateCity>
>
> When I export the results from my query
>
> SELECT
>       STATELIST.state_code, EMPLOYEE.city
> FROM
>       STATELIST
>           INNER JOIN EMPLOYEE ON STATELIST.state_code = EMPLOYEE.state
> GROUP BY
>       STATELIST.state_code, EMPLOYEE.city
> ORDER BY
>       STATELIST.state_code, EMPLOYEE.city;
>
> the XML comes out like this.
>
> <StateCity>
> <state_code>AK</state_code>
> <city>Anchorage</city>
> </StateCity>
> <StateCity>
> <state_code>AK</state_code>
> <city>Cantwell</city>
> </StateCity>
> <StateCity>
> <state_code>AK</state_code>
> <city>Cooper Landing</city>
> </StateCity>
>
> Anyone know how to show the relationship in the export?