If a column value in the table will be temporarily transformed into XML variables-CodePudding

Colleagues have so a stored procedure, need to XML type @ ArrayParams variable
Declare @ ArrayParams XML
The SET @ ArrayParams='& lt; ParamItems>
A001 & lt;/item>
B002 & lt;/item>
'
The EXEC stored procedure name @ ArrayParams

I need to call the stored procedure, but parameters A001, B002... Through a series of query before the call, I get a temporary table # Temp, content is as follows:
No.
A001
A002
A003
B001
C001
C002
:
:
:
Consult the great god, how will the temporary table information into @ ArrayParams variables, used to invoke the colleagues of the stored procedure?
At the same time ask the XML variable capacity limits and such? Like my temporary table has 100000 rows of data, into XML is workable?

CodePudding user response:

 
The create table # Temp (item varchar (50)) 

Insert into # Temp (item) 
Select 'A001' union all 
Select 'A002' union all 
Select 'A003' union all 
Select 'B001' union all 
Select 'C001' union all 
Select 'C002' 

Declare @ ArrayParams XML 

Select @ ArrayParams=(select item 
The from # Temp 
For XML path (' '), root (' ParamItems)) 

Select @ ArrayParams 

/* 
 
 A001 & lt;/item> 
 A002 & lt;/item> 
 A003 & lt;/item> 
 B001 & lt;/item> 
 C001 & lt;/item> 
 C002 & lt;/item> 
 
*/

CodePudding user response:

XML variable capacity of 2 gb.