An Alternative XML Solution

• Wayne-153714

  Hall of Fame

  Points: 3536

  More actions

  October 13, 2004 at 11:28 am

  #361196

  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

  More actions

  November 10, 2004 at 7:23 am

  #529609

  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>