Yukon has enhanced the SQL Server 2000 FOR XML function. If you want to create element based XML (yay!), FOR XML PATH allows you to specify column aliases that contain valid XPath expressions that will shape your XML output.
--XML FOR PATH
Select
ContactID as [@Contact_ID],
FirstName as [ContactName/First],
LastName as [ContactName/Last],
Phone as [ContactPhone/Phone1]
From Person.Contact For XML PATH
Produces output like:
<Customer>
<CustomerID>1</CustomerID>
<OrderDetail>
<SalesOrderID>43860</SalesOrderID>
<OrderDate>2001-08-01T00:00:00</OrderDate>
</OrderDetail>
<OrderDetail>
<SalesOrderID>44501</SalesOrderID>
<OrderDate>2001-11-01T00:00:00</OrderDate>
</OrderDetail>
</Customer>
<Customer>
<CustomerID>2</CustomerID>
<OrderDetail>
<SalesOrderID>46976</SalesOrderID>
<OrderDate>2002-08-01T00:00:00</OrderDate>
</OrderDetail>
<OrderDetail>
<SalesOrderID>49054</SalesOrderID>
<OrderDate>2003-02-01T00:00:00</OrderDate>
</OrderDetail>
<OrderDetail>
</Customer>
If you are familiar and comfortable with XPath, there are some additional features to XML PATH you may like. You can use the following XPath node test functions to further control the shape of your XML output:
· node()
· text()
· data()
· comment()
· processing-instruction()