XML SQL Query not displaying nested elements

[Craftor]

Hi all

Not sure if this question is best displayed in this forum or the SQL forum so redirect me if necessary.

I have a SQL stored procedure to retrieve data through an inner join on 2 tables. I have checked the query using a 'normal' (non-XML) query and it outputs fine.

The query looks something like this:

SELECT
1 AS Tag,
NULL AS Parent,
'itemUser' as [services!1!requesttype!element],
0 as [services!1!returnvalue!element],
NULL AS [user!2!id!element],
NULL AS [user!2!loginname!element],
NULL AS [user!2!password!element],
NULL AS [user!2!organisationid!element],
NULL AS [user!2!organisationname!element],
NULL AS [user!2!bookersgroup!element],
NULL AS [user!2!companyid!element],
NULL AS [user!2!companyname!element],
NULL AS [bookuser!3!usertype!element],
NULL AS [bookuser!3!userid!element],
NULL AS [bookuser!3!bloginname!element],
NULL AS [bookuser!3!bpassword!element]


UNION

-- Populate XML
SELECT
2 AS Tag,
1 AS Parent,
NULL,
NULL,
UID,
UcLoginName,
UcPassword,
UiOrganisationID,
UcOrganisationName,
UcBookersGroup,
UiCompanyID,
UcCompanyName,
BiUserType,
BiUserID,
BcLoginName,
BcPassword
FROM dbo.[User]
INNER JOIN dbo.BookUser ON UID = BiOrganisation
WHERE UID = 1
AND (BiUserType = 1 OR BiUserType = 2)

ORDER BY [user!2!loginname!element]

FOR XML EXPLICIT

It retrieves all elements up to user ... but nothing of bookuser! There should be at least 2 book users for every user.

I have never done an SQL XML query before so I'm a bit unsure here.

Any help or advice please?

Thanks as always

Craftor