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.
