SQL Server Formatting with FOR XML

Question

I have a query that spits out an XML format from two tables in the database. However, I'm not getting the correct format I wanted.

Below is the query:

SELECT  m.MovieID                           AS MovieID,
        m.MovieName                         AS MovieName,
        mt.Description                      AS MovieType,   
        ISNULL((SELECT  ms.ShowDateTime         AS ShowDateTime
                FROM    MovieShowtime ms WITH(NOLOCK)
                WHERE   ms.MovieID = m.MovieID
                ORDER BY ms.ShowDateTime ASC
                FOR XML PATH('Showtime'), TYPE
                ), '')                      AS MovieShowtimes
FROM    Movie m WITH(NOLOCK)
INNER JOIN MovieType mt WITH(NOLOCK)
        ON mt.MovieTypeID = m.MovieTypeID
FOR XML PATH('Movie'), ROOT('Cinema'), TYPE

Below is the output:

<Cinema>
    <Movie>
        <MovieID>1234</MovieID>
        <MovieName>Harry Potter and the Half-Blood Prince</MovieName>
        <MovieType>Fantasy</MovieType>
        <MovieShowtimes>
            <Showtime>
                <ShowDateTime>2019-08-26T19:30:00</ShowDateTime>
            </Showtime>
            <Showtime>
                <ShowDateTime>2019-08-27T19:30:00</ShowDateTime>
            </Showtime>
            <Showtime>
                <ShowDateTime>2019-08-28T19:30:00</ShowDateTime>
            </Showtime>
        </MovieShowtimes>
    </Movie>
</Cinema>

However, my intended output should be formatted as follows:

<Cinema>
    <Movie>
        <MovieID>1234</MovieID>
        <MovieName>Harry Potter and the Half-Blood Prince</MovieName>
        <MovieType>Fantasy</MovieType>
        <MovieShowtimes>
            <ShowDateTime>2019-08-26T19:30:00</ShowDateTime>
            <ShowDateTime>2019-08-27T19:30:00</ShowDateTime>
            <ShowDateTime>2019-08-28T19:30:00</ShowDateTime>
        </MovieShowtimes>
    </Movie>
</Cinema>

I'm fairly new to XML so I would really appreciate a push in the right direction to get this formatted correctly.

Answer 1

Just remove the Path('Showtime') from your MovieShowtimes query and replace it with Path('')

select      m.MovieID        as MovieID
            , m.MovieName    as MovieName
            , mt.Description as MovieType
            , isnull((   select     ms.ShowDateTime as ShowDateTime
                         from       MovieShowtime as ms with (nolock)
                         where      ms.MovieID = m.MovieID
                         order by   ms.ShowDateTime asc
                         for xml path(''), type)
                     , '')   as MovieShowtimes
from        Movie     as m with (nolock)
inner join  MovieType as mt with (nolock) on mt.MovieTypeID = m.MovieTypeID
for xml path('Movie'), root('Cinema'), type