ancestor using XQuery in SQL Server 2005

Hi all,
I'm need to use the ancestor axis in an XQuery statemen. However, i receive
the next error message:
Msg 9335, Level 16, State 1, Procedure pa_Sales_Test, Line 76
XQuery [query()]: The XQuery syntax 'ancestor' is not supported.
Does anyone know how to workaround the problem?
This is an example of the XQuery statement:
SELECT @.xml.query('
for $GM in descendant::dsc:GeneratedMaterial
return
(
<GeneratedMaterial
Reference="{ data($GM/@.material) }"
Color="{ data($GM/@.color) }"
Quantity="{ data($GM/@.count) }"
Length="{ data($GM/@.length) }"
Height="{ data($GM/@.height) }"
AncestorNode="{ data($GM/ancestor::*/Hole/@.id) }"
/>
)
')
The ancestor Hole may be located anywhere in the xml, as well as the
GeneratedMaterial node.
Thanks in Advance,
Alberto.You can find all the hole elements in the document that have the current
GeneratedMaterial as a descendant. Here is an example:
CREATE
--ALTER
FUNCTION BData()
RETURNS XML
AS
BEGIN
RETURN N'<build>
<stuff>
<hole id="1">
<more>
<GM material="A" color="red"/>
<GM material="B" color ="blue"/>
</more>
</hole>
<hole id="2">
<more>
<GM material="C" color="red"/>
<GM material="D" color ="blue"/>
</more>
</hole>
<hole id="3"></hole>
<hole id="10">
<hole id="4">
<more>
<GM material="F" color="red"/>
<GM material="G" color ="blue"/>
</more>
</hole>
</hole>
<GM material="H" color ="blue"/>
</stuff>
</build>'
END
SELECT dbo.BData().query(
'
for $GM in (descendant::GM)
return element GM {
attribute material {$GM/@.material},
attribute color {$GM/@.color},
attribute AncestorId {
(:
get the holes that has this GM as a descendant,
that is that are an ancestor of GM
:)
//hole[descendant::GM[. is $GM]]/@.id}
}
')
---
<GM material="A" color="red" AncestorId="1" />
<GM material="B" color="blue" AncestorId="1" />
<GM material="C" color="red" AncestorId="2" />
<GM material="D" color="blue" AncestorId="2" />
<GM material="F" color="red" AncestorId="10 4" />
<GM material="G" color="blue" AncestorId="10 4" />
<GM material="H" color="blue" AncestorId="" />
Note that some GM elements in this document have more than one hole as an
ancestor and others have no holes and ancestors.
Dan

> Hi all,
> I'm need to use the ancestor axis in an XQuery statemen. However, i
> receive
> the next error message:
> Msg 9335, Level 16, State 1, Procedure pa_Sales_Test, Line 76
> XQuery [query()]: The XQuery syntax 'ancestor' is not supported.
> Does anyone know how to workaround the problem?
> This is an example of the XQuery statement:
> SELECT @.xml.query('
> for $GM in descendant::dsc:GeneratedMaterial
> return
> (
> <GeneratedMaterial
> Reference="{ data($GM/@.material) }"
> Color="{ data($GM/@.color) }"
> Quantity="{ data($GM/@.count) }"
> Length="{ data($GM/@.length) }"
> Height="{ data($GM/@.height) }"
> AncestorNode="{ data($GM/ancestor::*/Hole/@.id) }"
> />
> )
> ')
> The ancestor Hole may be located anywhere in the xml, as well as the
> GeneratedMaterial node.
> Thanks in Advance,
> Alberto.
>|||Hi Dan,
Great example. Just one question:
What does //hole mean?
Does it return all hole(s) of the document or just the ones in the second
level of the xml?
"Dan" <dsullivanATdanal.com> escribi en el mensaje
news:964a9ae651b1d8c85db66c8af312@.news.microsoft.com...
> You can find all the hole elements in the document that have the current
> GeneratedMaterial as a descendant. Here is an example:
> CREATE --ALTER
> FUNCTION BData()
> RETURNS XML
> AS
> BEGIN
> RETURN N'<build>
> <stuff>
> <hole id="1">
> <more>
> <GM material="A" color="red"/>
> <GM material="B" color ="blue"/>
> </more>
> </hole>
> <hole id="2">
> <more>
> <GM material="C" color="red"/>
> <GM material="D" color ="blue"/>
> </more>
> </hole>
> <hole id="3"></hole>
> <hole id="10">
> <hole id="4">
> <more>
> <GM material="F" color="red"/>
> <GM material="G" color ="blue"/>
> </more>
> </hole>
> </hole>
> <GM material="H" color ="blue"/>
> </stuff>
> </build>'
> END
>
> SELECT dbo.BData().query(
> '
> for $GM in (descendant::GM)
> return element GM {
> attribute material {$GM/@.material}, attribute color {$GM/@.color},
> attribute AncestorId {
> (:
> get the holes that has this GM as a descendant,
> that is that are an ancestor of GM
> :)
> //hole[descendant::GM[. is $GM]]/@.id}
> }
> ')
> ---
> <GM material="A" color="red" AncestorId="1" />
> <GM material="B" color="blue" AncestorId="1" />
> <GM material="C" color="red" AncestorId="2" />
> <GM material="D" color="blue" AncestorId="2" />
> <GM material="F" color="red" AncestorId="10 4" />
> <GM material="G" color="blue" AncestorId="10 4" />
> <GM material="H" color="blue" AncestorId="" />
>
> Note that some GM elements in this document have more than one hole as an
> ancestor and others have no holes and ancestors.
> Dan
>
>
>|||//hole is a synonym for /descendant-or-self::hole which means all the hole
elements in the document.
Dan
> Hi Dan,
> Great example. Just one question:
> What does //hole mean?
> Does it return all hole(s) of the document or just the ones in the
> second
> level of the xml?
> "Dan" <dsullivanATdanal.com> escribi en el mensaje
> news:964a9ae651b1d8c85db66c8af312@.news.microsoft.com...
>