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
Author
Message
Posted Wednesday, October 13, 2004 11:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 6:24 AM
Points: 143, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/WFillis


When in doubt - test, test, test!

Wayne

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 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>

 




Post #145624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse