XML from SP into an XML field question

Question

XML from SP into an XML field question

bvaljalo-1000038

Right there with Babe

Points: 760
More actions
November 2, 2009 at 1:07 pm

#135904

Okay, I'm pretty new to using XML, but I have this stored procedure in my DB that I need to keep intact and working as it does now. In reality, it is quite long and complicated, but I'll just provide an simplified example of what it looks like:
create proc spXample (@pk int)
As
select * from tblFoo where pk = @pk
FOR XML PATH ('bar'), ROOT ('foo')
;
Now, as I say, we have production processes that use this sp 'as is' so I really can't change it. I *also* do not want to have to maintain the logic in multiple places, because in reality this xml is very long and complex, and I'm already maintaining it in two places, I really don't want to add more.
What I DO want to be able to do is to leverage the existing SP in order to populate a simple table that looks like so:
create table xmlfoo
(pk int, Xample xml)
Where the field Xample would contain the XML generated by the spXample for each row in tblFoo.
Is there any way to write a function or SP that would allow me to use my existing SP in order to populate the XML field in xmlFoo, WITHOUT changing spXample, and without simply duplicating it's entire Select statement somewhere else (which obviously would make the whole process rather simple, but I'm trying to avoid)?
TIA,
Brett

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Is it simple enough XML that you could have a computed column that stored it? Or an indexed view?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

bvaljalo-1000038 Right there with Babe Points: 760 More actions · Answer 2

Unfortunately my requirements are rather specific and not entirely up to me.

In it's most basic form, here's my question:

Given the following stored proc:

create proc spXample (@pk int)

As

select * from tblFoo where pk = @pk

FOR XML PATH ('bar'), ROOT ('foo')

;

is it possible to do something along the lines of this (which doesn't work as is):

declare @listxml xml;

exec @listxml = dbo.spXample 1;

(without changing how spXample operates in the eyes of outside services that call the procedure) and such that the variable @listxml would be populated with the XML that the SP generates?

And if so, how?

Thanks again 🙂

bvaljalo-1000038 Right there with Babe Points: 760 More actions · Answer 3

Nevermind, I figured it out ...

Create function fnXample (pk int)

returns xml

as

begin

declare @xml xml

select @xml =

(

select * from foo

where pk = @pk

FOR XML PATH ('bar'), ROOT ('foo')

)

return @xml

end

;

And I just changed my SP to look like this:

create proc spXample (@pk int)

as

Select fnXample(@pk)

;

So now the complicated SQL to generate this XML still lives in one spot, and by selecting from this function, my stored proc works the same + I can now easily populate the XML field in the table as well via the function.