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 formulated based on the order of the tables identified by columns specified in the select clause. The top element in XML is Customers as Name and Country belongs to the Customers table. The second element in XML is the subsequent table name that you mentioned in SQL Query.
The Name and Country columns are referring the Customers table so <Customers> elements is created and Name,Country are added as its attributes. The next three columns in select clause referring Orders table so <Orders> formed as an element and columns are added as its attributes.
Assume you want to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format
The XML is now element centric so , The SQL query can be written as below with ELEMENTS option
