How to select an attribute value from an XML-column using XPath/XQuery?

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi,

I have stored some XML data in a XML-column in one of my tables. It looks
something like this:

<Parameters>
<Parameter Name="View" Value="A" />
<Parameter Name="@HistDate" Value="2007-02-12" />
<Parameter Name="@ManagerID" Value="null" />
<Parameter Name="@SystemUserID" Value="2" />
</Parameters>

My XML will always have one Parameter-element with the attribute
Name="@SystemUserID", and I want to be able to select its value.
I have tried, but I dont get it to work.

If I use the following question, I can select the right Parameter element:

Select
Parameters.query('/Parameters/Parameter[@Name="@SystemUserID"]') As
SystemUserIDElement
From
MyTable

Parameter is the name of my XML-Column.

I get this result:

<Parameter Name="@SystemUserID" Value="2" />

That seems right. But I want to select the value of the Value attribute. In
this case "2".

I think that I should be able to do that by running this query:

Select
Parameters.value('/Parameters/Parameter[@Name="@SystemUserID"]/@Value',
'int') As SystemUserIDValue
From
MyTable

But all I get is this error message:

Msg 2389, Level 16, State 1, Line 6
XQuery [MyDB.dbo.MyTable.Parameters.value()]: 'value()' requires a singleton
(or empty sequence), found operand of type 'xdt:untypedAtomic *'

I guess there is a really simple solution to this. But I cant find any good
help on the web. So if anyone could help me with this I would be very
greatful.

If someone has a good link to a site that has information on how to
query/select data from XML Data Columns in Sql Server 2005 that would be
very appreciated.

Thanks,

Robert


.