September 05, 2007

XML AUTO in SQL 2005

Mark has sent a tip which automatically generates an XML document as the output of the query using "FOR XML AUTO" functionality.

Adding the elements keyword lets you nest nodes. E.g. It outputs an INVC record with nested HIST items.




select * from invc I inner join HIST H on I.VENDID=H.VENDID and I.INVOICE=H.INVOICE and I.EXPPED=H.EXPPED where I.invoice='testUI1' FOR XML AUTO, elements

Pretty slick and a nice time saver when you need a quick XML doc to test with. There is extensive help in SQL on FOR XML AUTO.