Another problem with XQuery!

Hi all,

In my Sql Server 2005 database I have a column called OtherInformation which is an XML datatype. Within this column I have XML in the following format:

<Logs>

<Event Id="123">

<Information>Database crash</Information>

</Event>

</Logs>

The value of the Id attribute can change but I an trying to write a query that will retrieve the value of the Information element when the Id attribute of the Event element is 123. What I have so far is below but this returns the text regardless of what the attributes value is!

SELECT

LogId, OtherInformation.query('Logs/Event/Information/text()') As EventInformation

FROM

Logs

WHERE

OtherInformation.exist('Logs/Event[@.Id]') = '123'

Any help on this would be great. Thanks!

You need this query:

Code Snippet

SELECT LogId, OtherInformation.query('Logs/Event/Information/text()')

FROM Logs

WHERE OtherInformation.exist('Logs/Event[@.Id = "123"]') = 1;

|||Fantastic Martin. That works great!

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...
>