Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

An Alternative XML Solution Expand / Collapse
Posted Wednesday, October 13, 2004 11:28 AM


Group: General Forum Members
Last Login: Sunday, November 1, 2015 10:51 AM
Points: 143, Visits: 79
Comments posted to this topic are about the content posted at

When in doubt - test, test, test!


Post #141557
Posted Wednesday, November 10, 2004 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 2, 2007 9:05 AM
Points: 6, Visits: 1

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 GROUPRECORDS' as _shapeInfo
select * from orderItems where clientID=@clientID

results in:

<client id='1234' name='Client Name'...>
    <order id='92342' orderDate='2004-10-12'>
            <item id='00093' description='Skippy 12oz.' quantity='3'/>
            <item id='00093' description='Welch's 8oz.' quantity='2'/>


Post #145624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse