Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Summary
Microsoft SQL Server has a powerful XML generation feature that became extremely simple to use with the PATH mode introduced in the SQL Server 2005 release. The PATH mode uses SELECT column names to indicate where the individual column values should appear in the final XML document. The XPATH expressions supported by the PATH mode can indicate that a column value should appear as a child element (you can even build a complex tree of descendants), an attribute, or the text contents of the current XML element. You can even use nested queries to generate complete XML fragments that are then spliced into the final XML document.
In this article, you've seen how you can use the PATH mode to generate a hierarchical XML document formatted according to Atom standard with a Transact-SQL stored procedure, resulting in faster execution and an extremely simple server-side script. In an environment where a majority of XML documents are generated on the SQL server, you could even develop a universal server-side script that would accept the name of a stored Transact-SQL procedure together with its parameters, execute the procedure, and return the results tagged as XML data to the client.