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>
<SalesOrderID>44501</SalesOrderID>
<OrderDate>2001-11-01T00:00:00</OrderDate>
</Customer>
<CustomerID>2</CustomerID>
<SalesOrderID>46976</SalesOrderID>
<OrderDate>2002-08-01T00:00:00</OrderDate>
<SalesOrderID>49054</SalesOrderID>
<OrderDate>2003-02-01T00:00:00</OrderDate>
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()
Page rendered at Wednesday, August 12, 2020 11:41:52 PM (Eastern Daylight Time, UTC-04:00)
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.