Tags: #microsoft, #mssql, #XML, Server, SQL
I found this very elegant way to generate XML directly from an SQL Server query:
lastName AS [LastName],
firstName AS [FirstName],
adressLine AS [Address/Line],
adressZip AS [Address/Zip],
adressCity AS [Address/city],
emailAddress AS [ContactDetails/emailaddress],
website1 AS [ContactDetails/websites/website/website1],
website2 AS [ContactDetails/websites/website/website2],
telephone AS [ContactDetails/telephone]
FOR XML PATH (‘Employees’) , ROOT (‘Employee’), ELEMENTS
This query will generate you the (nested) tags including the headers of the file.
The only limitation that I found with this method is the length of the identifier. If the number of characters of the identifier is over 128, SQL server will reject your query. As a workaround, you can create aliases of your identifiers [ContactDetails/emailaddress] = [CD/EmAd] for example and replace the values once the XML has been generated.