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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


The problem is that the XPath expression must _statically_ resolve to a
singleton.

In the case of untyped XML an expression like
/Parameters/Parameter[@Name="@SystemUserID"] statically infers
element(Parameter, xdt:untyped)*

The star indicates that we can expect 0 or more of these elements.

In order to infer a singleton you can add a position predicate like this:
(/Parameters/Parameter[@Name="@SystemUserID"])[1]. The static type becomes
element(Parameter, xdt:untyped)?

The question mark indicates that we expect 0 or 1 of these elements. The
singleton requirement is met.

A while ago I addressed this category of problems on my blog. Here's the link:
http://blogs.msdn.com/denisruc/archive/2005/06/14/429055.aspx

I hope this helps

Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm


"Robert Pettersson" <robpet@xxxxxxxxxxx> wrote in message
news:%23HgPoakhHHA.3452@xxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • RE: XQuery using a Parameter with .nodes
    ... The workaround would be to build the entire SQL query dynamically ... There is no way to build the XPath expression from a variable like you did. ... in the XML datatype's methods. ... Declare @FormXMLXPath nvarchar, ...
    (microsoft.public.sqlserver.xml)
  • Re: text/string finder
    ... >> I'm new to xml and sometimes gives me a hardtime. ... >> that returns a particular string I'm looking for? ... > This is not a syntactically correct XPath expression. ... > containing the string 'foo', ...
    (microsoft.public.dotnet.xml)
  • Parsing a log4j configuration file
    ... I've been trying to parse a log4j configuration file using ... DocumentBuilderFactory dbfact = DocumentBuilderFactory.newInstance; ... an XPath expression? ... I've used this code to get values from other XML ...
    (comp.lang.java.programmer)
  • Re: How to use XPath expression to parse XML document and using XSL - i.e. do a selective XSLT
    ... convert that XPathNodeIterator back into an XPathNavigator, ... Dim doc As XmlDocument = New XmlDocument ... XPathNodeIterator) because an XML document ... and then inside of the xsl refer to this in the XPath expression like so: ...
    (microsoft.public.dotnet.xml)
  • Re: Manipulating or comparing xs:dateTime values in xpath queries with SelectNodes
    ... Does it return an empty XmlNodeList, ... (I can't be of much help identifying why the XPath expression doesn't ... You're going to have to manipulate the date in your ExpireDate ... compare strings. ...
    (microsoft.public.dotnet.xml)