Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ailuro
Starting Member
8 Posts |
 Posted - 2002-05-01 : 11:31:02
|
| Hiya,I'm attempting to use the FOR XML EXPLICIT clause to get an XML string that looks how I like it. Instead of specifying everything in the long, horrible query that usually results from a complex EXPLICIT, I thought I would try embedding the XML from sub nodes as stored proceedures within my base universal table. Basically, I want the Address proceedure to worry only about making the Address XML, and my Person proceedure to grab not only basic things about the Person, but also include the XML from the Address proceedure.That probably didn't make any sense, so I'll just ask the questions :)1) Is there a way to use the results of another proceedure within a SELECT statement as a single field. IE, is there a replacement for this nonfunctional statement:SELECT 1 as tag, NULL as parent, EXEC(mesp_Address_GetXMLByList) as AddressNode2) Is there a better method of getting a stored proc a list of things other than manually building a delimited list? This didn't work:Call with: mesp_Address_GetXMLByList @ListGen = 'SELECT PersonAddress.AddressID FROM PersonAddress WHERE PersonAddress.PersonID = 1'Use within GetXMLByList:[Select stuff...]WHEREAddress.AddressID IN (EXEC(@ListGen))The EXECUTE function doesn't like me, won't let me use it anywhere I really want to. Any ideas on how to handle this would be much appreciated. If nothing else I'll just go back to my huge EXPLICIT statements :)Thanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-01 : 11:43:40
|
| You can't SELECT from a stored procedure, you would instead write the stored procedure to return the results you want to SELECT. If you are using the stored procedure to generate data to be used in other SELECT queries, you can try building the SELECT statement using dynamic SQL:http://www.sqlteam.com/item.asp?ItemID=4599http://www.sqlteam.com/item.asp?ItemID=4619In all truth, if you are going to build XML from SQL data, you are better off using the FOR XML methods without trying to shortcut it or do something fancy. I can't imagine that the EXPLICIT clause will actually be worse than any patch job you might be able to use.If you don't already have it, go getThe Guru's Guide to SQL Server Stored Procedures, XML and HTMLby Ken Henderson. He covers a lot of XML tips and tricks and has some means to handle certain shortcomings like the one you're seeing now. Not to mention that it is the best book on it's subject. You can get it at the SQL Team Bookstore. |
 |
|
|
ailuro
Starting Member
8 Posts |
Posted - 2002-05-01 : 11:59:46
|
| Ah, I was afraid I was going a bit overboard. Thanks for setting me straight, and thanks for the book recommendation :) |
|
|
|
|
|
|
|
|
For XML Explicit...