How to make a FOR XML AUTO output of an Inner Joined SELECT output both tables to their own respective Elements?

Question

I'm attempting to create a stored proc that uses a BIT variable to print out one of 2 possible FOR XML results of a Inner joined Select statement. Here is the current form of the proc:

CREATE PROCEDURE dbo.CustomerAddress_Get
@IsAuto BIT = 0
AS
BEGIN
--@IsAuto If's
IF (@IsAuto == 0)
    BEGIN

    SELECT LEFT(GivenName,1), Surname, DOB, AccountId, Type, Balance FROM Custs
    INNER JOIN dbo.Accounts 
    ON Accounts.CustNo = Custs.CustNo
    FOR XML RAW ('CustomerAccount'), ELEMENTS, ROOT('CustomerAccountDetails')

    END
ELSE
    BEGIN

    SELECT LEFT(GivenName,1), Surname, DOB, AccountId, Type, Balance FROM Custs Customer
    INNER JOIN dbo.Accounts Account
    ON Account.CustNo = Customer.CustNo
    FOR XML AUTO, ELEMENTS, ROOT

    END
END;

And the code I'm specifically trying to alter to output Accounts and Customers to their own Elements in the XML AUTO Output is this:

SELECT LEFT(GivenName,1), Surname, DOB, AccountId, Type, Balance FROM Custs
INNER JOIN dbo.Accounts 
ON Accounts.CustNo = Custs.CustNo
FOR XML AUTO, ELEMENTS, ROOT

An example of my current XML output from my Else SELECT...FOR AUTO; statement is thus:

<Customer>D<Surname>Grayson</Surname><DOB>1940-04-01T00:00:00</DOB><Account><AccountId>159</AccountId><Type>Chequing</Type><Balance>0.0000</Balance></Account><Account><AccountId>160</AccountId><Type>Savings </Type><Balance>500.0000</Balance></Account></Customer>

However, I require it to be more like this:

<cu>
<FirstName>Catherine</FirstName>
<LastName>Abel</LastName>
<EmailAddress>[email protected]</EmailAddress>
<ca>
<AddressType>Main Office</AddressType>
<ad>
  <City>Van Nuys</City>
  <StateProvince>California</StateProvince>
</ad>

Which is what I believed the FOR XML AUTO was for, however it doesn't seem to be actually formatting the resulting XML.

Now, my question relative to this is how exactly can I make the this SELECT put the Customer (GivenName, Surname, and DOB/DateOfBirth) and Accounts (AccountId, Type, and Balance) their own respective details in their own Elements in the XML AUTO output?

Answer 1

This is an answer to the question according to your title using a dynamically created statement:

CREATE PROCEDURE dbo.CustomerAddress_Get
@IsAuto BIT = 0
AS
BEGIN
--@IsAuto If's
DECLARE @cmd NVARCHAR(MAX)=
(
    N'SELECT LEFT(GivenName,1), Surname, DOB, AccountId, Type, Balance FROM Custs
    INNER JOIN dbo.Accounts 
    ON Accounts.CustNo = Custs.CustNo
    FOR XML '
);

IF @IsAuto = 0
    SET @cmd=@cmd+N'RAW (''CustomerAccount''), ELEMENTS, ROOT(''CustomerAccountDetails'')';
ELSE
    SET @cmd=@cmd+N'AUTO, ELEMENTS, ROOT';

EXEC (@cmd);
END;

UPDATE: Your second question

If you use FOR XML PATH you can control the final format of your XML

Without knowing your tables it's hard to suggest the right solution (the given data and examples do not match!), but it should be something like this:

SELECT LEFT(cu.GivenName,1) AS [cu/FirstLetter]
      ,cu.GivenName AS [cu/FirstName]
      ,cu.Surname AS [cu/LastName]
      ,cu.DOB AS [cu/Birthdate]
      ,cu.City AS [ad/City]
      ,cu.StateProvinve AS [ad/StateProvince]
      ,ac.AccountId AS [ac/AccountId]
      ,ac.Type AS [ac/Type]
      ,Balance AS [ac/Balance] 
FROM Custs AS cu
INNER JOIN dbo.Accounts AS ac
ON ac.CustNo = cu.CustNo
FOR XML PATH('Customer'),ROOT('root')

In this case I'd suggest not to use the approach with dynamically created SQL as given above, but to stick to your existing IF ... ELSE ...

UPDATE 2: Sub-select instead of join

You might try something like this. The code will create the embedded XML as sub-select:

SELECT Custs.*
     ,(
        SELECT Accounts.* 
        FROM dbo.Accounts 
        WHERE Accounts.CustNo = Custs.CustNo
        FOR XML AUTO, ELEMENTS,TYPE
      ) AS [ad]
FROM Custs AS [cu]
FOR XML AUTO, ELEMENTS, ROOT