Building the XML Response with XSLT
The XML response returned by the SQL server can be fine-tuned to produce any XML structure you need, using the FOR XML EXPLICIT option. However, the EXPLICIT option is quite convoluted, requiring custom-built queries with complex structure and specially crafted column names. In some cases, a better option is to accept the simple XML response returned by the SQL server and use server-side XSLT translation to produce the desired XML format.
The only way to perform an XSLT translation in the ASP (or ASP.NET) environment is to parse the string returned by the SQL server into a DOM tree by using the loadXML method of a DOM document object and applying the transformation to the parsed tree, which results in a significant performance hit. It’s best to avoid this approach, unless you want to implement a rarely used transaction in which the existing client application dictates the XML format of the results.