Nested XML stored procedures? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Nested XML stored procedures?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-12-08 : 16:09:48

I'm working on converting parts of a web app to use XML/XSL for rendering (from recordsets and ASP-based HTML generation).

I've run into a tough one, though. It's a community based app, and a ton of stored procedures need to return data about the relationship(s) (if any) between the viewing users and the user being viewed. To that end, we've got a standard SP that generates that data, and it appears at the beginning of lots of other SP's. That is, lots of other SP's call this "p_relationships" SP as the first thing they do, then do their own thing. The ASP code similarly has a standard function to parse that data, of course.

The idea being to minimize round trips; the same thing *could* be done by turning every one of the calls to those other SP's into two DB calls, but that's ugly.

Moving to XML, it doesn't look like I can do that. For instance,


create procedure px_test0 as 
set nocount on
select 1 as tag,
	null as parent,
	123 as [test1!1!value!element]
for xml explicit


select 1 as tag,
	NULL as parent,
	456 as [test2!1!value!element]
for xml explicit

create procedure px_test1 as 
set nocount on
select 1 as tag,
	null as parent,
	123 as [test1!1!value!element]
for xml explicit

create procedure px_test2 as
set nocount on
exec dbo.px_test1

select 1 as tag,
	NULL as parent,
	456 as [test2!1!value!element]
for xml explicit

The first produces the results I want; a single XML document with two nodes (test1 and test2). However, trying to split them up in px_test2 (which calls px_test1) produces two *resultsets*, each with a single XML document.

Is there some way around this? ASP doesn't support streaming XML from multiple recordsets, so it looks like I either have to go for the two-round-trip approach (ugly), or parse the recordsets as strings, glue the strings together, and create my XML object from the combined string (really ugly).

Advice?
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-12-08 : 17:39:02

You know, I really have to quit smoking crack.

Never mind me. Nothing to see here. Move along.

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources