Hi,
XML EXPLICIT queries tend to get very complicated. But, here is the basic T-SQL that you're looking for.
-TH
SELECT 1 as Tag,
NULL as Parent,
L.LenderID as [Lender!1!lenderID],
L.LenderName as [Lender!1!lenderName],
NULL as [Location!2!locationID],
NULL as [Location!2!locationName],
NULL as [Contact!3!contactID],
NULL as [Contact!3!contactName]
FROM Lenders
UNION ALL
SELECT 2 as Tag,
1 as Parent,
L.LenderID,
NULL,
LOC.LocationID,
LOC.LocationName,
NULL,
NULL
FROM Lenders L, Locations LOC
WHERE L.LenderID = LOC.LenderID
UNION ALL
SELECT 3 as Tag,
2 as Parent,
L.LenderID,
NULL,
LOC.LocationID,
NULL,
C.ContactID,
C.ContactName
FROM Lenders L, Locations LOC, Contacts C
WHERE L.LenderID = LOC.LenderID
AND LOC.LocationID = C.LocationID
ORDER BY [Lender!1!lenderID], [Location!2!locationID], [Contact!3!contactID]
FOR XML EXPLICIT
--- In flexcoders@yahoogroups.com, "Tracy Spratt" <[EMAIL PROTECTED]> wrote:
>
> Sure, that just looks like a couple joins. I don't do enough SQL to
> spit out any code but that requirement wouldn't worry me.
>
>
>
> A hierarchy like you describe is most easily represented in XML. What I
> do is build the query using FOR XML AUTO. Then I just pass that
> resulting xml back to the Flex client.
>
>
>
> Tracy
>
>
>
> ________________________________
>
> From: flexcoders@yahoogroups.com [mailto:[EMAIL PROTECTED] On
> Behalf Of lostinrecursion
> Sent: Friday, September 15, 2006 1:40 PM
> To: flexcoders@yahoogroups.com
> Subject: [flexcoders] SQL Query to populate Flex Object (CF Backend)
>
>
>
> Hi all,
>
> The destination is Flex and although my question is a little Backend
> oriented, I assume that many developers have faced this.
>
> I have a VO in my application called Lenders.
>
> Each Lender has x number of Locations (a collection of VOs represented
> by an ArrayCollection called lender.locations)
>
> Each Location has x number of Contacts (another collection of VOs
> represented by an ArrayCollection called
> lender.selectedLocation.contacts)
>
> Anyway, the question is simple. I have 3 tables in a SQL Server 2005
> DB which I use Coldfusion to query.
>
> The database tables (which are related by FK ids) will populate the
> object.
>
> is there anyway for the SQL Query to do that work? Meaning a hierarchy?
>
> I would want it to return like this.
>
> Lender
> Location
> Contact
> Contact
> Location
> Contact
> Location
>
> You get the idea.
>
> is that possible or do I have to loop over the Queries in Coldfusion
> and build them into structs?
>
> Thanks.
>
--
Flexcoders Mailing List
FAQ: http://groups.yahoo.com/group/flexcoders/files/flexcodersFAQ.txt
Search Archives: http://www.mail-archive.com/flexcoders%40yahoogroups.com
| Software development tool | Software development | Software development services |
| Home design software | Software development company |
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch to Fully Featured
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
__,_._,___
