hi all,

At the moment I am working on the interfacing between the Business Layer and some other subsystems. We choose to pass the data to the subsystems using Xml. But now i am chewing on a elegant way to get the following done. I have tables like these:

PaymAdvice
----------
ID (PK)
PaymentDate

PaymAdviceDetail
----------------
ID (PK)
PaymAdviceID (FK to PaymAdvice)
VendorID (FK to Vendor)
Amount

Vendor
------
ID (PK)
Name

There is n:m relation between the PaymAdvice and the Vendor tables via the PaymDetail. Getting an entity collection and sorting it on VendorID is straightforward using sortclauses. However, I need to be able to create an Xml message with the following structure

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<PaymAdvice ID="...">
    <PaymentDate>a date</PaymentDate>
    <Vendor ID="vendor1">
        <Name>Name of vendor1</Name>
        <Payments>
            <Payment ID="001">
                <Amount>1200</Amount>
            </Payment>
            <Payment ID="003">
                <Amount>400</Amount>
            </Payment>
        </Payments>
    </Vendor>
    <Vendor ID="vendor2">
        <Name>Name of vendor2</Name>
        <Payments>
            <Payment ID="002">
                <Amount>2560</Amount>
            </Payment>
        </Payments>
    </Vendor>
</PaymAdvice>

I will need this kind of groupings a lot of times, so I'm looking for a method that is efficient and generally applicable. Is this possible or am I asking too much?

THe 'best' way is to use an XSLT and convert the xml retrieved from WriteXml() to the format you need, that's what XSLT is for. Writing XSLT is pretty straight forward.