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 characters needed characters left characters exceeded

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

1 Answer

· Write an 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 characters needed characters left characters exceeded

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

Bring it along the "SELECT * Coffee Bar"... ;-)
2 Likes 2 ·
Might pick your brains at SQL Saturday if that's OK?
1 Like 1 ·
As you say, ouch. I'll know which stored procedure needs which path but how can I code that? Thanks
0 Likes 0 ·
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 ·
See what you mean, that's nasty but if it's the only way . . . . Will see how important it is!!
0 Likes 0 ·
There may be another way of creating the XML, but without the ease of PATH. Not sure. But maybe another option to look at.
0 Likes 0 ·
Look forward to it!
0 Likes 0 ·

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.