question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

Parameterise option FOR XML PATH

Does anyone know if it's possible to use a parameter to return the path in the "For XML Path('@PARM') We have a stored procedure that holds the XML format and is called by many stored procedures. Originally the Path was the same in all cases however I have been asked to change it depending on which Stored Procedure calls it and therefore the only way I can think to do this is to turn it into a parameter but I can't see how to make this work. All ideas gratefully received. Many thanks
sql-server-2008-r2xmlparameters
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Ouch. Is the set of paths small and known? Can you code for each possible setting? Otherwise you might have to use dynamic SQL. Neither of those are nice.
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Bring it along the "SELECT * Coffee Bar"... ;-)
2 Likes 2 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
Might pick your brains at SQL Saturday if that's OK?
1 Like 1 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
As you say, ouch. I'll know which stored procedure needs which path but how can I code that? Thanks
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Pass the sp name as a param, and then have a horrible if @param in (sp1,sp2,sp3) begin do some stuff select .... FOR XML PATH('cheese') end if @param in (sp4,s5,sp6) begin do some stuff select .... FOR XML PATH('butter') end (dairy based elements optional)
0 Likes 0 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
See what you mean, that's nasty but if it's the only way . . . . Will see how important it is!!
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.