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.
