Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

Using FOR XML Clause - SQL Server

RSS
Modified on Tue, May 26, 2009, 9:31 AM by Administrator Categorized as SQL Server, XML, XSL, and XPath
The following is a sample usage of the FOR XML clause in a SELECT statement.

/*
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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.