RE: Help with 'for xml explicit', SQL 2000

I was able to resolve this, mostly, by adding an 'order by' clause. Thanks to
the following articles, which were the only ones to stress the importance of
the 'order by' clause:

http://www.sqlmag.com/Article/ArticleID/19810/In_Control_with_FOR_XML_EXPLICIT.html
http://sqlxml.org/faqs.aspx?faq=28

I still get empty <rdom/> tags on records with <udom> elements, but those
are easy enough to tidy up after the fact.

Lee Anne

"Lee Anne" wrote:

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
.

Relevant Pages

  • Help with for xml explicit, SQL 2000
    ... I need some help with a 'for xml explicit' query on SQL Server 2000. ... select 1 as tag, ...
    (microsoft.public.sqlserver.xml)
  • Re: Limiting the amount of rows
    ... > it occurs in the database ie ... What I did was use an IN query to pull in just the raw totals for the ... query does not use a TOP clause because we are limited by the HAVING. ... Group By Attr ...
    (microsoft.public.sqlserver.server)
  • Re: nother XHTML question...
    ... Strict it doesn't like the "name" attribute in img tags.. ... course there are many ways around using the "name" attr.. ... the reason is that the tag is outside the div where the img is.. ... I had no idea that eventhandlers all had to be lowercase... ...
    (comp.infosystems.www.authoring.html)
Loading