Wrox Programmer Forums
BOOK: Professional Microsoft SQL Server 2008 Programming
This is the forum to discuss the Wrox book Professional Microsoft SQL Server 2008 Programming by Robert Vieira; ISBN: 9780470257029
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional Microsoft SQL Server 2008 Programming section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 30th, 2010, 09:39 AM
Registered User
Join Date: Apr 2010
Posts: 10
Thanks: 1
Thanked 1 Time in 1 Post
Wink Setup SQL so results are in XML

Hi Everyone! Hi Rob!
I have been working through some of the exercises in the XML chapter and they are very very good. Thanks for all of the details, notes and comments you added, plus really easy to understand code.

I know that you can use a FOR XML to direct a query output to XML, but is there a way to setup SQL Server 2008 so the query results are in XML? For the default? I cannot figure out the steps on how to setup SQL server so the query results are in XML, and have also searched online a dozen ways to figure this out.

I can create XML output from a query using the examples but I want to actually setup SQL to do this.

Thanks for any advice you might be able to offer! This one is stumping me.
Have a great day and weekend!

Ann Ziluck

Aspiring SQL ASP and VS geek, full time online student, IT Manager, former AS/400 guru :)
Old July 26th, 2010, 11:17 AM
Registered User
Join Date: Jul 2010
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Getting XML from SQL

As I understand it, XML is a data communication language, and does not have a system for storage and retrieval. To do this in SQL, you should create a SPROC that matches the XML and call it from your queries with parameters to match the elements and attributes you are looking for within the XML data you have stored in SQL Server. The way SQL Server stores and handles XML data is pretty complicated, which is why using SPROCs to handle the application programming interface (API) for accessing and manipulating it is the best practice for getting XML out of the system.

By providing your users with the necessary SPROCs, you will effectively remove the barrier for them to get to the XML by giving them an SQL friendly interface to the create, read, update, delete (CRUD) routines. You can produce a SPROC for ever table that stores XML data so that rows can be worked on as though they were entirely standard scalar data values. I think some of the confusion comes from the way that XML stores data is a potentially deep hierarchy of what we would know as columns in SQL. Combining those two paradigms is tough, so using a SPROC that is designed to work with the XML which is essentially hanging off the edge of the table row by a pointer will solve most any problems with getting XML from the SQL Server database in XML format.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Cant get SQL setup correctly topher76 BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 18 January 30th, 2010 05:51 AM
Relational SQL Data to XML - Vet SQL/ASP - New XML JimiTheJett XML 1 December 4th, 2008 06:06 PM
SQL returning odd results jakeone SQL Language 1 November 13th, 2006 05:07 AM
$sql not returning results, this cant be this hard cspgsl Beginning PHP 0 May 5th, 2006 11:50 AM
Translating a XML with XSL and saving results DevOne XML 1 April 9th, 2005 07:32 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.