An Alternative XML Solution

  • Wayne-153714

    Hall of Fame

    Points: 3536

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/WFillis


    When in doubt - test, test, test!

    Wayne

  • Hugh Lynch

    SSC Enthusiast

    Points: 116

    There are indeed many solutions to creating XML from SQL Server.  I have had good success for several years using stored procedures that return multiple recordsets with embedded instructions for converting them to XML.  The only non-SQL code required is a generic utility class that executes the stored procedures, then interprets and converts the results.  For example:

    create procedure shpClientOrders( @clientID int ) as

    select 'DATASET clients.client' AS _shapeInfo

    select * from clients where id=@clientID

    select 'DATASET orders.order RELATE TO clients' as _shapeInfo

    select * from orders where clientID=@clientID

    select 'DATASET orderItems.item RELATE orderID to orders.id GROUPRECORDS' as _shapeInfo

    select * from orderItems where clientID=@clientID

    results in:

    <client id='1234' name='Client Name'...>

        <order id='92342' orderDate='2004-10-12'>

            <items>

                <item id='00093' description='Skippy 12oz.' quantity='3'/>

                <item id='00093' description='Welch's 8oz.' quantity='2'/>

            </items>

        </order>

    </client>

     

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply