Sahil just posted a nice little primer for getting started with the FOR XML clause in sql 2005. Good start, however I feel like he missed a technique.
One of my major gripes with working with FOR XML is when you have to join more than 2 tables in your query. FOR XML AUTO tends to create a child node for each join. Say for example you want to show a list of customers, their contact information (address and phone being separate tables) and their order history. With a default join using FOR XML you'll end up with something like this:
<Customer name="blah"><Address element="blah" element2="blah"><Phone element="blah"><order etc></order></phone></address></customer>
Now to me, this xml makes absolutely no sense. Customers have a single primary address and phone number, and orders belong to customer, not phone number, (or however you ordered your join). So while you can use Sahil's techniques to do explicit building of your XML, there is a much easier way:
Create views.
If you create a view that pre-joins customer, address, and phone number and a view that compiles your order information, you can do a FOR XML AUTO query on the view joins and life will be good, with a minimum of effort. And I'm all about achieving results in the easiest, most readable, maintainable way possible. =)
Print
posted @ Tuesday, February 20, 2007 12:01 PM