Subject:Format DB xQuery results Author:Mike Lucas Date:17 Apr 2009 02:30 PM
I am trying to build an xQuery against three database tables, and return the results in a hierarchy format. My key field across each table is con_id, and the detail table has one or more records for each record in the master table. I can see in my syntax that the format is currently:
<Cons>
<master>
<detail></detail>
<ridr></ridr>
</master>
</Cons>
And I want to change it to:
<Cons>
<master></master>
<detail></detail>
<ridr></ridr>
</Cons>
Everything I've tried has returned syntax errors or told me that line 8 cannot find $m. My xQuery is below.
<Cons>
{
for $m in collection("DB.dbo.master")/master
return
<master>
{ $m/* }
{
for $d in collection("DB.dbo.detail")/detail[con_id = $m/con_id]
return
<detail>
{ $d/* }
</detail>
}
{
for $r in collection("DB.dbo.ridr")/ridr[con_id = $m/con_id]
return
<ridr>
{ $r/* }
</ridr>
}
</master>
}
</Cons>