Removing XML-Tags in nested SELECT-Statement from FOR XML PATH() in SQL Server 2014

Question

I need to remove the xml-tags from the nested sql statement:

SELECT STUFF(
    (
        SELECT Mailbox
        FROM dbo.Mailbox
        WHERE UnitGroup IN (
            SELECT a.Groups
            FROM (
                SELECT DISTINCT ID, GroupFC AS Groups
                FROM dbo.v_Pending

                UNION ALL

                SELECT DISTINCT ID, GroupBN AS Groups
                FROM dbo.v_Pending

                UNION ALL

                SELECT DISTINCT ID, GroupRM AS Groups
                FROM dbo.v_Pending
            ) AS a
            WHERE a.Groups IS NOT NULL
            AND a.ID = 12345
        )
        FOR XML PATH('')
    ), 1, 0, ''
) AS Mails

I tried it with the statement FOR XML PATH('') but it doesn't work. Maybe someone knows the error. I always get the xml-tags -.-

Currently result looks like this:

<Mailbox>[email protected];</Mailbox>
<Mailbox>[email protected];</Mailbox>

What do you want it to return?

S3S
– S3S

2019-08-22 16:47:11 +00:00
Commented Aug 22, 2019 at 16:47 — S3S
– S3S, Commented Aug 22, 2019 at 16:47

yuro · Accepted Answer · 2019-08-22 16:47:59Z

1

I found the solution for my problem. I had to add in my nested SELECT-Statement an empty string '':

SELECT STUFF(
    (
        SELECT '' + Mailbox
        FROM dbo.Mailbox
        WHERE UnitGroup IN (
            SELECT a.Groups
            FROM (
                SELECT DISTINCT ID, GroupFC AS Groups
                FROM dbo.v_Pending

                UNION ALL

                SELECT DISTINCT ID, GroupBN AS Groups
                FROM dbo.v_Pending

                UNION ALL

                SELECT DISTINCT ID, GroupRM AS Groups
                FROM dbo.v_Pending
            ) AS a
            WHERE a.Groups IS NOT NULL
            AND a.ID = 12345
        )
        FOR XML PATH('')
    ), 1, 0, ''
) AS Mails

answered Aug 22, 2019 at 16:47

yuro

2,2296 gold badges43 silver badges79 bronze badges

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

Collectives™ on Stack Overflow

Removing XML-Tags in nested SELECT-Statement from FOR XML PATH() in SQL Server 2014

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related