Posts

Showing posts with the label XML AUTO

FOR XML Clause With Join

Image
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...

Transforms Rows Into XML Elements: FOR XML AUTO

This post we'll explore the XML AUTO mode. FOR XML AUTO     By default:         No nesting         Use attribute-centric presentation.         Element/row tag names = schema.table or the table alias.         It's not allowed to change the row/element tag names by specifying an argument for AUTO mode.         Note it's only allowed to specify an argument to RAW or PATH mode of FOR XML to change the element/row tag name.                 Example:                 SELECT custid                               ,contactname                 FROM Sales.Customers                 FOR XML RAW('Customers'); By adding the ELEMENT directive, the xml ...