How to insert rows to value

Have table with values

CREATE TABLE #tbl 
    (
       id int  identity(1,1),
       object_type int ,
       object_id nvarchar(50),
       refl nvarchar(4000)
    )

refl value have parametres like :

<prov><CProv><object_type>1</object_type><object_id>152</object_id></CProv></prov>
<prov><CProv><object_type>0</object_type><object_id>150</object_id></CProv></prov>
<prov><CProv><object_type>0</object_type><object_id>156</object_id></CProv></prov>
<prov><CProv><object_type>1</object_type><object_id>172</object_id></CProv></prov>

I want to select all data which is in refl to another value declare @val nvarchar(4000) . Is it possible ? When I try select @val = refl from #tbl it select only last row, I want to select all rows. How do it? It must be like

@val = N'<prov><CProv><object_type>1</object_type><object_id>152</object_id></CProv></prov>
    <prov><CProv><object_type>0</object_type><object_id>150</object_id></CProv></prov>
    <prov><CProv><object_type>0</object_type><object_id>156</object_id></CProv></prov>
    <prov><CProv><object_type>1</object_type><object_id>172</object_id></CProv></prov>'

Answer

Try this

DECLARE @val NVARCHAR(4000) 
SELECT @val = COALESCE(@val + '', '') + refl
FROM #tbl

SELECT @val

Advertisement

Answer