InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 00:54:16 GMT20http://www.sqlservercentral.com/Forums/Topic141557-192-1.aspx<P>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:</P><P>create procedure shpClientOrders( @clientID int ) asselect 'DATASET clients.client' AS _shapeInfoselect * from clients where <A href="mailto:id=@clientID">id=@clientID</A>select 'DATASET orders.order RELATE TO clients' as _shapeInfoselect * from orders where <A href="mailto:clientID=@clientID">clientID=@clientID</A>select 'DATASET orderItems.item RELATE orderID to orders.id GROUPRECORDS' as _shapeInfoselect * from orderItems where <A href="mailto:clientID=@clientID">clientID=@clientID</A>results in:</P><P>&lt;client id='1234' name='Client Name'...&gt; &lt;order id='92342' orderDate='2004-10-12'&gt; &lt;items&gt; &lt;item id='00093' description='Skippy 12oz.' quantity='3'/&gt; &lt;item id='00093' description='Welch's 8oz.' quantity='2'/&gt; &lt;/items&gt; &lt;/order&gt;&lt;/client&gt;</P><P> </P>Wed, 10 Nov 2004 07:23:00 GMTHugh Lynchhttp://www.sqlservercentral.com/Forums/Topic141557-192-1.aspxComments posted to this topic are about the content posted at <A HREF=http://www.sqlservercentral.com/columnists/WFillis/analternativexmlsolution.asp>http://www.sqlservercentral.com/columnists/WFillisWed, 13 Oct 2004 11:28:00 GMTWayne-153714