For XML clause in Select Query allows you to return the query results in XML format. You can specify Auto, RAW, EXPLICIT and PATH in For XML clause. This post shows an example on Auto Mode. Auto Mode generates the nested XML as a result based on the column name order that you have specified in the select clause. The Auto mode can be used for generating simple XML hierarchies as a result.
Define the tables as shown in the following picture
In order to display the XML as a result
you can write the following query
The XML is now element centric so , The SQL query can be written as below with ELEMENTS option