FOR XML Clause With Join
Today, we will explore how to transforms rows into XML elements when dealing with joining multiple tables. The examples below will demonstrate the important of the columns ordering when using join. -- Example 1. -- This query returns the first 2 customers' orders -- by INNER JOIN Sales.Customers and Sales.Orders table SELECT c.custid, c.contactname, o.orderid, o.orderdate FROM Sales.Customers AS c JOIN Sales.Orders AS o ON o.custid = c.custid WHERE c.custid <= 2; Result: The customers' orders are not ordered based on the custid. Without the ORDER BY clause, there's no guarantee of the rows ordering. -- Example 2 --In this query, ORDER BY clause is not specified and result is transformed to XML. SELECT c.custid, c.contactname, o.orderid, o.orderdate FROM Sales.Customers AS c JOIN Sales.Orders AS o ON o.custid = c.custid WHERE c.custid <= 2 FOR XML AUTO; Result: Notice just like, in term of ordering, the result of this query is the same as previo...