Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Using FOR XML Clause - SQL Server

/*
exec dbo.GetCustomers 0,1
exec dbo.GetEngagements '<x v="4BA3AE24-6164-4063-A185-CCEBF9F5C0C9"/>'
exec dbo.GetProjects '<x v="14F2E27B-012D-4192-8343-3015B1792340"/>'

exec dbo.GetProgramLevelClients 
    '<x v="6F2377F0-98C1-4F44-A0BC-31D1BD26EE22"/>'
    + '<x v="4BA689CB-D269-4F7F-848C-2A10544BFE6C"/>'
    + '<x v="6A87EA34-94D8-4189-AFEA-9A3BE2E309E7"/>'
    + '<x v="3459FDF5-B193-47DC-A6C0-8E894FF60EC3"/>'
    + '<x v="14CB3619-EEDE-415E-8A58-C35E13CBDDE3"/>'
    + '<x v="74EEC1BA-3FCA-4DC6-AF68-A87C97D37E78"/>'
*/
alter procedure [dbo].[GetProgramLevelClients] 
    (
    @CpProjectIds        xml
    ) as begin

select distinct
     ClientName = cpp.WbClientName
    ,ClientId    = cpp.ClientId
    ,Projects    = (
            select "x/@v" = convert(varchar(40), CpProjectId)
            from dbo.ChangepointProjects(@CpProjectIds) cpp2
            where cpp.ClientId = cpp2.ClientId
            order by CpProjectId
            for xml path ('')
            )
                
from
    dbo.ChangepointProjects(@CpProjectIds) cpp
order by
     ClientName

end