ramlicious Blogs by Tina & Prabhu

October 7, 2010

Selecting data in SQL Server as Hierarchical XML

Filed under: SQL Server — Prabhuram @ 9:11 am

I will show it in two lines (one the actual query and then the result) and the rest you can search from msdn.

Select 
	OrderId,
	(select 
		ProductId as "@ProductId", 
		UnitPrice as "@UnitPrice", 
		Quantity as "@Quantity", 
		UnitPrice*Quantity as "@Amount" from Northwind..[Order Details] 
	 Where OrderId = Orders.OrderId For XML Path('Product'), Type) as "Products",
	OrderDate
From Northwind..Orders
For XML Path('Order'), Elements XSINIL,
Root('Orders')

You can see the SQL by yourself here. xml.sql (363.00 bytes)

The SQL demostrates usage of For XML, showing columns as attributes or as elements and one level or hierarchy.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress