Cross apply function returning XML needs a column name

Question

We are cleaning our database of accounts which share email addresses. Some email addresses are used on as many as 20 accounts, but we are moving to a new system that doesn't permit this.

What I am trying to do here is create a list of email addresses, a count of how many times they are used and a third column with an HTML table with some details of the account. These tables will be emailed to the associated address to ask them to fix their accounts.

By itself, the function works correctly:

SELECT MembershipNumber td, Username td, FirstName td, Lastname td, Telephone td, Mobile td 
FROM [dbo].[DuplicateEmailAccounts] ('someone@example.com')
FOR XML RAW('tr'), ELEMENTS, ROOT('table')

This produces an XML response with an HTML table listing all the associated accounts. All good so far.

The problem starts when I try and join it to the list of email addresses - the final output will be a CSV file to upload to mailchimp to do a mail merge operation. When I include the function in a larger SQL statement, I get the error:

Msg 8155, Level 16, State 2, Line 26 No column name was specified for column 1 of 'accounts'.

Any suggestions on how to fix this will be most appreciated!

select m.E_mail, count(*) MemberCount, accounts.*
    from Members m
CROSS APPLY (
(SELECT MembershipNumber td, Username td, FirstName td, 
    Lastname td, Telephone td, Mobile td 
    FROM [dbo].[DuplicateEmailAccounts] (E_mail)
    FOR XML RAW('tr'), ELEMENTS, ROOT('table')) ) accounts
where m.E_mail is not null and dbo.trim(m.E_mail) <> ''
    group by m.e_mail
    having count(*) > 1
    order by count(*) desc

Shoundn't your cross apply be a subquery in the `SELECT`? – Thom A Nov 29 '18 at 16:21 — Thom A, Nov 29 '18 at 16:21

Shnugo · Accepted Answer · 2018-11-29T16:34:24.280

I cannot reproduce this with your tables, therefore an example using some dummy queries against tables existing in any SQL-Server database. Try it out and adapt your query to your needs:

SELECT TOP 3 o.*
            ,accounts.*
FROM sys.objects o
CROSS APPLY
(
    (SELECT TOP 3 t.create_date td, t.durability_desc td FROM sys.tables t FOR XML RAW('tr'),ELEMENTS,ROOT('table'))
) accounts(x)

Hint: You can - if needed - add ,TYPE after the ROOT() to get a typed XML back.

Hint2: Make sure, that there are no NULL values in your resultset. NULLs are omited and will lead to a shifted broken table layout...

Btw: In this answer there is a function, which creates a rich HTML-table directly out of any query... Might be helpful..

I dig the rich HTML table. – John Cappelletti Nov 29 '18 at 16:35 — John Cappelletti, Nov 29 '18 at 16:35

Cross apply function returning XML needs a column name

1 Answers1