Styjun

I have a simple XML:

DECLARE @x1 xml = '<r>
 <o a="1">
 <o a="2">
 </o>
 </o>
</r>';

And select the following:

SELECT r.o.value('(../@a)[1]','varchar(20)') FROM @x1.nodes('/r//o') r(o);

Everything is nice:

NULL
1

When I use typed XML:

CREATE XML SCHEMA COLLECTION [dbo].test AS '
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <xsd:element name="r">
 <xsd:complexType>
 <xsd:complexContent>
 <xsd:restriction base="xsd:anyType">
 <xsd:sequence>
 <xsd:element name="o" type="o" minOccurs="0" maxOccurs="unbounded" />
 </xsd:sequence>
 </xsd:restriction>
 </xsd:complexContent>
 </xsd:complexType>
 </xsd:element>
 <xsd:complexType name="o">
 <xsd:complexContent>
 <xsd:restriction base="xsd:anyType">
 <xsd:sequence>
 <xsd:element name="o" type="o" minOccurs="0" maxOccurs="unbounded" />
 </xsd:sequence>
 <xsd:attribute name="a" type="xsd:string" />
 </xsd:restriction>
 </xsd:complexContent>
 </xsd:complexType>
</xsd:schema>'
GO

DECLARE @x2 xml(dbo.test) = '<r>
 <o a="1">
 <o a="2">
 </o>
 </o>
</r>';

SELECT r.o.value('(../@a)[1]','varchar(20)') FROM @x2.nodes('/r//o') r(o);

I got the Error:

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Why does the untyped XML works and the typed XML not?

It looks as though you well understand that this is not the usual problem of having to convince the parser that there is only one thing being passed into value(). This problem is specific to typed XML, as you have observed.

Using google I found this ancient blogpost with the promising text

rt is part of the XQuery 1.0/XPath 2.0 data model.

Most people get by that. The real fun starts when you do examples using untyped XML and XPath expressions with the text() node test. text() works just fine when using untyped XML, but fails against typed XML with simple content

It talks about the SQL Books Online and the SQL Server 2005 XML Best Practices paper, which I think is this book here. I haven't looked for a more up-to-date reference. But using what I found there I have been able to fix your problem: Simple replace

SELECT r.o.value('(../@a)[1]','varchar(20)') FROM @x2.nodes('/r//o') r(o);

with

SELECT r.o.value('fn:string(../@a)[1]','varchar(20)') FROM @x2.nodes('/r//o') r(o);

Basically, value() doesn't like being given values of a typed-xml-type, but wants strings. So we give it a string.