For xml explicit question

[simonchristieis]

I have a stored proc that returns xml

CREATE PROCEDURE [dbo].[Select_Test] 
 
AS
BEGIN
SET NOCOUNT ON;
 

Select 1							as Tag,
	null							as Parent,
	null							as [Level2!1],
	''								as [Level3!2]

Union All
 
Select 2							as Tag,
	1								as Parent,
	null							as [Level2!1],
	''								as [Level3!2]
For Xml Explicit 
 
END

I want to call this proc and create a larger xml document, using a wrapper root node, I suppose I want to append the results to the first node

CREATE PROCEDURE [dbo].[Select_MultipleTest] 
 
AS
BEGIN
SET NOCOUNT ON;
 

select '<Level1>'

Exec Select_Test
Exec Select_Test
Exec Select_Test

select '</Level1>'

 
END

Anyone know how I can do this ?

 

[mrdenny]

Sure.

CREATE PROCEDURE [dbo].[Select_Test] 
   @XML as XML OUTPUT 
AS
BEGIN
SET NOCOUNT ON;
 
SET @XML = (
Select 1                            as Tag,
    null                            as Parent,
    null                            as [Level2!1],
    ''                                as [Level3!2]

Union All
 
Select 2                            as Tag,
    1                                as Parent,
    null                            as [Level2!1],
    ''                                as [Level3!2]
For Xml Explicit )
 
END

Then for the calling procedure.

CREATE PROCEDURE [dbo].[Select_MultipleTest] 
 
AS
BEGIN
SET NOCOUNT ON;
 
DECLARE @XML as XML
DECLARE @BigXML as NVARCHAR(MAX)

set @BigXML = '<Level1>'

Exec Select_Test @XML = @XML OUTPUT
set @BigXML = @BigXML + cast(@XML as nvarchar(max))
Exec Select_Test @XML = @XML OUTPUT
set @BigXML = @BigXML + cast(@XML as nvarchar(max))
Exec Select_Test @XML = @XML OUTPUT
set @BigXML = @BigXML + cast(@XML as nvarchar(max))

set @BigXML = @BigXML +  '</Level1>'

select cast(@BigXML as XML) as 'XML'
END

(Untested and may need tweaking.)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com