How to create view or function on XML PATH query?

I am using SSMS 2008 with the following query:

DECLA开发者_开发问答RE @TestData TABLE 
( 
     address_desc NVARCHAR(100)  NULL 
    ,people_id UNIQUEIDENTIFIER  NULL 
); 

INSERT  @TestData 
SELECT a.address_desc, a.people_id 
FROM dbo.address_view a 

SELECT  a.people_id, 
    (SELECT SUBSTRING( 
        (SELECT ';'+b.address_desc 
        FROM    @TestData b  
        WHERE   a.people_id = b.people_id 
        FOR XML PATH('')) 
        ,2 
        ,4000) 
    ) GROUP_CONCATENATE 
FROM    @TestData a 
GROUP BY a.people_id 

This query works, but I want to make this into a view or function so that I can call it from different stored procs. How can I do this? From what I understand, variables cannot be declared in VIEW statements.

Hong, here is my updated query based on your advice which gives me errors:

DECLARE @TestData TABLE 
( 
     address_desc NVARCHAR(100)  NULL 
    ,people_id UNIQUEIDENTIFIER  NULL 
); 

INSERT  @TestData  
SELECT a.address_desc, a.people_id  FROM dbo.address_view a 

SELECT  a.people_id, 
(SELECT address_desc, people_id FROM dbo.address_view),
    (SELECT SUBSTRING( 
        (SELECT ';'+b.address_desc 
        FROM    @TestData b  
        WHERE   a.people_id = b.people_id 
        FOR XML PATH('')) 
        ,2 
        ,4000) 
    ) GROUP_CONCATENATE 
FROM    @TestData a 
GROUP BY a.people_id 

---

In your last select query replace @TestData with subquery (SELECT address_desc, people_id FROM dbo.address_view), and then get rid of temp table @TestData.

Try this:

Create View YourView As
    SELECT  a.people_id,      
            (SELECT SUBSTRING(          
                    (SELECT ';'+b.address_desc          
                     FROM    (SELECT address_desc, people_id FROM dbo.address_view) b           
                     WHERE   a.people_id = b.people_id          
                     FOR XML PATH(''))          
                     ,2          
                     ,4000)      
                     ) GROUP_CONCATENATE  
    FROM    (SELECT address_desc, people_id FROM dbo.address_view) a  
    GROUP BY a.people_id