XML PATH output one by one using SQL Server

Question

I'm trying to group column values by a specific column using FOR XML PATH('') in SQL Server.

For example when I pass the ModuleID value 1 then output RoleName : Admin Administrator Super Admin which is present is same line.

ALTER PROCEDURE [dbo].[MEDEIL_SiteRoleModules_SelectOne] 
    @ModuleID int
AS
    SELECT 
        t1.ModuleID, 
        RoleName = (SELECT s1.RoleName AS [data()] 
                    FROM SiteRoleModules t2 
                    INNER JOIN SiteRoles s1 ON t2.RoleID = s1.RoleID 
                    WHERE t2.ModuleID = t1.ModuleID 
                    GROUP BY s1.RoleName 
                    FOR XML PATH('')) 
    FROM
        SiteRoleModules t1 
    WHERE
        ModuleId = @ModuleID 
    GROUP BY 
        ModuleID

I need to display one by one

Doesn't make sense to use Where ModuleId = 1 with Group since your code will only return one Group. Remove the Where. — jdweng
– jdweng, Commented Dec 8, 2017 at 5:43
Is it working? Does the database have data for Admin, Administrator, and Super Admin? — jdweng
– jdweng, Commented Dec 8, 2017 at 5:57
Use String_Agg and make seperator a 0x0A or 0x0D. See : learn.microsoft.com/en-us/sql/t-sql/functions/… — jdweng
– jdweng, Commented Dec 8, 2017 at 6:11

Emre Kabaoglu · Accepted Answer · 2017-12-08 07:48:56Z

1

You can just try to use CHAR(10) to break lines;

SELECT 
    t1.ModuleID, 
    RoleName = (SELECT CHAR(10) + s1.RoleName 
                FROM SiteRoleModules t2 
                INNER JOIN SiteRoles s1 ON t2.RoleID = s1.RoleID 
                WHERE t2.ModuleID = t1.ModuleID 
                GROUP BY s1.RoleName 
                FOR XML PATH('')) 
FROM
    SiteRoleModules t1 
WHERE
    ModuleId = @ModuleID 
GROUP BY 
    ModuleID

answered Dec 8, 2017 at 7:48

Emre Kabaoglu

13.2k4 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Emre Kabaoglu Over a year ago

Be careful that, management studio still display it same line but if you copy-paste the cell, you will see that are broken the lines.

Emre Kabaoglu Over a year ago

I said it made for an example. If you read the cell from your application or from somewhere, the column is got as "Admin\nSuperAdmin" etc.

Yogesh Sharma · Accepted Answer · 2017-12-08 05:54:03Z

0

You could try xquery.. .node method

select 
           case when row_number() over (partition by a.ModuleID order by a.ModuleID) > 1 then '' else cast(a.ModuleID as varchar) end [ModuleID],
           id.value('.', 'varchar(50)') [RoleName] from
     (
        select ModuleID ,  cast('<m>'+replace(RoleName, space(1), '</m><m>')+'</m>' as xml) RoleName  from <table>
     ) a cross apply RoleName.nodes ('/m') as RoleName(id)

Result :

ModuleID    RoleName
1           Admin
            Administrator
            Super
            Admin

answered Dec 8, 2017 at 5:54

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

3 Comments

Ivin Raj Over a year ago

invalid column name ModuleID and RoleName

Yogesh Sharma Over a year ago

@IvinRaj try to change the table name with your with appropriate column name.

Ivin Raj Over a year ago

can you add table name am new in this technology

Collectives™ on Stack Overflow

XML PATH output one by one using SQL Server

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related