SQL Server 2000 XML

The simplest way to generate nested XML from SQL Server is to use the FOR XML AUTO syntax:

SELECT root.version, parent.id, child.name
FROM (SELECT 1 AS version) AS root
JOIN parent ON (1 = 1)
JOIN child ON (child.parent_id = parent.id)
FOR XML AUTO

This will produce output such as:

<root version="1"><parent id="..."><child name="..."/></parent></root>

If you use the FOR XML RAW syntax instead, then one row element will be generated for each result row, e.g.

<row version="1" id="..." name="..."/>

For complete, control of the result set shape, use FOR XML EXPLICIT:

SELECT
  1 AS Tag,
  null as Parent,
  1 as [Root!1!version!hide],
  null AS [Case!2!id],
  null AS [ClaimantData!3!id!hide],
  null AS [ClaimantData!3!first_name],
  null AS [ClaimantData!3!last_name],
  null AS [ClaimantData!3!!xml]
UNION ALL
SELECT
  2 AS Tag,
  1 AS Parent,
  1,
  CaseData.id,
  null,
  null,
  null,
  null
FROM CaseData
UNION ALL
SELECT
  3 AS Tag,
  2 AS Parent,
  1,
  ClaimantData.case_id,
  ClaimantData.id,
  ClaimantData.first_name,
  ClaimantData.last_name,
  ClaimantData.confirmation
  from ClaimantData
JOIN CaseData ON (ClaimantData.case_id = CaseData.id)
ORDER BY 3,4,5
FOR XML EXPLICIT

This last option uses a so-called 'universal table' to define the XML document.  This first two columns assign tag numbers and parent-child relationships, and must be named Tag and Parent.  The remaining columns must contain the join keys and data, and be sorted into the desired document order.  The key and data columns must be named according to the convention ElementName!TagNumber!AttributeName!Directive.  The last two components are optional.  All this is quite ugly, but it does allow finer control of the final product.  Note, for example, how certain columns do not appear in the output (e.g. ClaimantData!3!id!hide) and how columns that contain XML data can be included in place (e.g. ClaimantData!3!!xml).

None of this stuff conforms to the emerging SQL/XML standard which, by all accounts, Microsoft has no intention of supporting.

ASP.NET

Here is a gotcha:  if you attempt to protect the contents of a directory using a web.config file like:

<location path="data">
    <system.web>
      <authorization>
        <deny users="*"/>
      </authorization>
    </system.web>
  </location>

... it won't work. ASP.NET only respects the configured permissions for files that it serves. Static directories, and most files in them, are served by IIS and must be protected using IIS settings.

ASP.NET

A nasty gotcha: ASP.NET stores the session identifier as a global cookie.  This has adverse affects upon session state in circumstances such as:

1. open your app in a browser window and browse to same page that carries session state
2. open your app in a second browser window and browse to a different page that has noticably different session state
3. return to the first browser and perform an action that relies upon the session state

You will notice that the first browser window has picked up state from the second.

This problem is not unique to ASP.NET, but affects any system that uses session cookies.  Depending upon the application, it might be safer to carry the session identifier in the URL or as variables on the page.

ADO.NET and ODBC

There is a way to monitor the ADO.NET connection pool.  Open the administrator applet Performance and add counters from the .Net CLR Data performance object.  There are various SqlClient counters.

In a related note, you can enable ODBC connection pool counters on the ODBC Data Source Administrator applet.  Click on the Connection Pooling tab and use the Enable setting under PerfMon.  I had to exit PerfMon and re-enter it to see the change.