Place "XML" Inside a Varchar Using FORXML 1

[astromenix]

Hi everyone
I got a stored proc that is getting pretty big, and at this point I got some repeated functionality, which sounds like I need to create a UDF or a stored proc and call it from my main proc.
Wot I want to achieve is pass to the function two sets of periods
For exaple:

FROM TO
05/06/2005 05/29/2005


FROM TO
05/07/2005 05/08/2005
05/12/2005 05/12/2005


FINAL ARRAY OF PERIODS

FROM TO
05/06/2005 05/06/2005
05/07/2005 05/08/2005
05/09/2005 05/11/2005
05/12/2005 05/12/2005
05/13/2005 05/29/2005


Then I need to create a new set of period where the second ones will overwrite the first ones.
I was thinking that my best bet would have been to use FORXML and pass the array of periods in an XML-format, as a comma delimiter solution wouldn’t be as easy to implement as far as I can think about it.
Is there a way to store the xml inside an varchar variable inside my stored procedure?

I tried the next one

DECLARE @FROM nvarchar(4000)
Select @FROM=PERIOD_FROM from PERIODS FOR XML RAW

But gives me an error
‘The FOR XML clause is not allowed in a ASSIGNMENT statement.’

Any ideas wot can I do?
Thanx in advance for any help or advice

 

[astromenix]

I just found on the MSDN that FOR XML is not valid in subselections

Guidelines for Using the FOR XML Clause

 

[JamesLean]

AFAIK there's no way to get the resultant XML string from a SELECT FOR XML into a local variable.

If you need to pass a list of values to a proc, try passing a comma-delimited list and parsing them into a temp table. Check out this FAQ for more info:

Passing a list of values to a Stored Procedure (Part II)
faq183-5207

--James

 

[astromenix]

thanx james
but i dont hink it will work for me..
cos i need two dimentional array

each row to contain one period and need two of them arrays
| periodfrom | period_to |
| date | date |
| date | date |
| date | date |
I could probably seperate with commas the period and another symbol the dates, but i not very sure whether is the r8 thing to do, although I'd have to perform that "breakin" of periods 3 times in my code.