Returning Multiple Disparate Result Sets as XML From SQL Server Using FOR XML EXPLICIT

OK.  So I’m finally returning to a SQL Server topic.  The integration tool I’m working on does a great deal of processing of XML.  In some cases, a single XML document consists of multiple result sets.  I won’t get into the details of why we need this, but a good example of where you might use it is to return an Order object from a database for an edit form and additional return a list of Order Types to populate the Order Type drop down box.  In this case the XML might look like this:

<orderformdata>
    <orders>
        <order>
            <orderdate>1/1/2011</orderdate>
            <ordertypeid>1</ordertypeid>
            <orderamount>1000.00</orderamount>
        </order>
        <order>
            <orderdate>1/1/2011</orderdate>
            <ordertypeid>1</ordertypeid>
            <orderamount>1000.00</orderamount>
        </order>
    </orders>
    <references>
        <ordertypes>
            <ordertype id="1" name="Professional Services"/>
            <ordertype id="2" name="Product"/>
            <ordertype id="3" name="Support Contract"/>
        </ordertypes>
    </references>
</orderformdata>

Using SQL Servers FOR XML clause, it’s quite easy to output a result set as XML.  With the EXPLICIT directive you can have a great amount of control over how the XML is rendered.  However, the examples tend to show how to create XML from a single result set or nested records (say orders and order details).  So how to return the above?

Fun with WCF, WSDL & the F5 (Updated)

So this week we pushed out an early iteration of the latest version of our integration tool to a customer as a proof-of-concept for evaluation.  One of the features they were most interested in evaluating is the web service interface.  Our current customers interact with the existing version of the integration tool through FTP and a UI. 

Although the backend functionality is fairly stable, the web service layer itself is not that mature.  Not too big a deal as it’s a fairly thin wrapper over our existing service layer.  We created, tested and delivered a test .NET client that seemed to work fine and so were reasonably confident that there wouldn’t be too many hiccups.

Not so much.  We spent the better part of the day simply trying to get their tool to retrieve and understand our WSDL.  The good news is that we finally got it to work.

Setting Up a Test Reverse Proxy/SSL Accelerator Environment (or for that matter, a budget production one)

In a previous post I talked about dealing with WCF services behind an F5 BIG-IP box (http://www.f5.com/products/big-ip/) that provides load balancing and SSL offloading.  I’ve actually found and developed solutions to the problems but needed to be able to properly test them.
Given that, according to our operations people, BIG-IP boxes start at 5 figures and rapidly go up, they seemed unwilling to purchase one for me.  Additionally, for security issues, and to remain in compliance with our SAS-70, I don’t have access to our production environment for testing.