Help with 'for xml explicit', SQL 2000

I need some help with a 'for xml explicit' query on SQL Server 2000. I don't
have any prior experience generating XML output from SQL.

I have the following table structure:

Metadata_ID
Entity_Type_Label
Attribute_Label <attrlabl>
Attribute_Definition <attrdef>
Range_Domain_Minimum <rdommin>
Range_Domain_Maximum <rdommax>
Unrepresentable_Domain <udom>

Compound primary key of Metadata_ID, Entity_Type_Label and Attribute_Label.

I need to extract the following XML snippets for each unique
Metadata_ID/Entity_Type_Label:

<attr>
<attrlabl>LOC_COORD_NORTHING</attrlabl>
<attrdef>Co-ordinate Y (northing) of location.</attrdef>
<attrdomv>
<rdom>
<rdommin>5.967861800000000e+006</rdommin>
<rdommax>6.139028100000000e+006</rdommax>
</rdom>
</attrdomv>
</attr>

or:

<attr>
<attrlabl>RD_VALUE</attrlabl>
<attrdef>Value - in system units.</attrdef>
<attrdomv>
<udom>Values do not exist in a known, predefined set.</udom>
</attrdomv>
</attr>

Each of these snippets can appear multiple times, with different Attribute
Labels.

I'm struggling on the nesting section of <attrdomv> with the fields being
from the same table, and being either <rdom> or <udom>. What I have so far
puts all of the <attrdomv> elements in the last <attr> node, instead of with
its respective node, as well as including a few empty tags:

<attr>
<attrlabl>Desc1</attrlabl>
<attrdef>Unit description, first part of paragraph</attrdef>
</attr>
<attr>
<attrlabl>LOC_COORD_NORTHING</attrlabl>
<attrdef>Co-ordinate Y (northing) of location.</attrdef>
</attr>
<attr>
<attrlabl>RD_VALUE</attrlabl>
<attrdef>Value - in system units.</attrdef>
</attr>
<attr>
<attrlabl>Unit_name</attrlabl>
<attrdef>Unit name</attrdef>
<attrdomv>
<udom>Values do not exist in a known, predefined set.</udom>
</attrdomv>
<attrdomv/>
<attrdomv/>
<attrdomv>
<udom>Values do not exist in a known, predefined set.</udom>
<rdom/>
<rdom>
<rdommin>5.967861800000000e+006</rdommin>
<rdommax>6.139028100000000e+006</rdommax>
</rdom>
<rdom>
<rdommin>-1.257000000000000e+003</rdommin>
<rdommax>7.930000000000000e+006</rdommax>
</rdom>
<rdom/>
</attrdomv>
</attr>

The query:

select 1 as tag,
null as parent,
aa.attribute_label as [attr!1!attrlabl!element],
aa.attribute_definition as [attr!1!attrdef!element],
null as [attrdomv!2!udom!element],
null as [rdom!3!rdommin!element],
null as [rdom!3!rdommax!element]
from amd_attributes aa
where metadata_id = @metadata_id and entity_type_label = @entity_type_label
union all
select 2 as tag,
1 as parent,
aa.attribute_label as [attr!1!attrlabl!element],
aa.attribute_definition as [attr!1!attrdef!element],
aa.unrepresentable_domain as [attrdomv!2!udom!element],
null,
null
from amd_attributes aa
where aa.metadata_id = @metadata_id and aa.entity_type_label =
@entity_type_label
union all
select 3 as tag,
2 as parent,
aa.attribute_label as [attr!1!attrlabl!element],
aa.attribute_definition as [attr!1!attrdef!element],
aa.unrepresentable_domain as [attrdomv!2!udom!element],
aa.range_domain_minimum as [rdom!3!rdommin!element],
aa.range_domain_maximum as [rdom!3!rdommax!element]
from amd_attributes aa
where aa.metadata_id = @metadata_id and aa.entity_type_label =
@entity_type_label
for xml explicit

I've tried doing aliased joins on the same table with no discernible
improvement.

I would appreciate any comments!

Thanks,
Lee Anne
.

Loading