Building nice XML from SQL Server Tables

The XML functionality of SQL Server is vast, however, the best subset of that functionality that I've found may be demonstrated in the following example:

Unfortunately, the example result is listed before the code.
  • Join all the desired tables together
  • Set the desired root element
  • Set the path to an empty string to avoid an extra XML element at the root of each resulting row
  • Provide an XPATH name with attributes for each of the desired columns (select * doesn't work well)
  • Keep all the selected columns together by XPATH name or you'll split your xml attributes

See my next blog entry for my best way to shred the xml

Comments

Popular posts from this blog

MSSQL Statistical Z-Score

IBM x335 and Windows 2008 installation

Database Projects, SQL Unit Tests, and TeamCity