For XML Explicit in a stored proc

17 Mar 2006 6:21 PM

john.wilker

I searched and searched and didn't see what (i think) i need for a
solution.

Here's my situation. SQL 2k5 Stored Proc with

set @XMLFile = (
SELECT
    1                        as Tag,
    NULL                    as Parent,
    cr.ID                    as [Resume!1!ID],
    cr.GUID                    as [Resume!1!GUID],
    cr.CandidateResumeTypeID    as [Resume!1!typeID],
    cr.ResumeName            as [Resume!1!Name],
    NULL                    as [resume_content!2!!ELEMENT],

lots and lots more.

ending in FOR XML EXPLICIT)

I get an error with this approach. The Query alone outside a proc and
outside the set blah blah = () returns an XML file. I need a proc to do
the same thing.

Thanks!

18 Mar 2006 3:02 AM

Kent Tegels

Hello john.wil***@gmail.com,

> ending in FOR XML EXPLICIT)
> I get an error with this approach. The Query alone outside a proc and
> outside the set blah blah = () returns an XML file. I need a proc to
> do the same thing.

Its always helpful to know the exact error, etc. but, have you considered
using FOR XML PATH instead? I've successfully used that many times in a stored
procedure exactly this way.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/