Odd results reading attributes when schema applied

Question

Odd results reading attributes when schema applied

andycadley

SSCertifiable

Points: 5296
More actions
August 7, 2018 at 11:35 pm

#104489

I was working on adding a schema to some XML and found that it would suddenly break some queries that worked fine without a schema definition (although the same XML could be stored in all cases). After many hours of hair pulling, I finally nailed it down to the fact I hadn't explicitly set a type on element attributes, although I'm still not entirely sure I understand why. A simplified version of this can be show with the following:
create xml schema collection xmlschema1 as
'<xsd:schema
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="root">
<xsd:complexType mixed="true">
<xsd:attribute name="id" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
'
create xml schema collection xmlschema2 as
'<xsd:schema
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="root">
<xsd:complexType mixed="true">
<xsd:attribute name="id" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
'
go
Declare @x0 XML = '<root id="33"/>'
Declare @x1 XML (xmlschema1) = '<root id="33"/>'
Declare @x2 XML (xmlschema2) = '<root id="33"/>'
Select X.N.value('@id', 'int') As v0 From @x0.nodes('root') X(N)
--Select X.N.value('@id', 'int') As v1 From @x1.nodes('root') X(N) -- This fails because the attribute isn't a singleton?
-- Error message is:
-- XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:anyAtomicType *'
Select X.N.value('@id', 'int') As v2 From @x2.nodes('root') X(N)
go
drop xml schema collection xmlschema1
drop xml schema collection xmlschema2

If you uncomment the second select, which uses the xmlschema1 schema, it fails with a compilation error that seems to suggest the attribute isn't a singleton (which I thought impossible) and with a schema like that I haven't found a single way to persuade it otherwise. Obviously the second schema variant works fine (so this isn't a breaking issue) but I was curious as to whether this is a bug in SQL Server or just some subtle aspect of XML/XPath that I'm missing.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

There are some possible factors which could cause this, one is that the XML only has a top level element, which can cause problems. The second thing is that although the breaking XML is typed, the attribute is still untyped and that changes the way the value is retrieved. Adding any applicable datatype definition will fix the problem.
😎

Mikael Eriksson SE SSCommitted Points: 1706 More actions · Answer 2

Select X.N.value('string(@id)', 'int') As v1 From @x1.nodes('root') X(N)

Here is a link to a similar case with an explanation on why by the designer of the XML datatype in SQL Server.

Found operand of type 'xdt:anyAtomicType *'. Why?!?!

andycadley SSCertifiable Points: 5296 More actions · Answer 3

Interesting, you learn something every day. Thanks chaps.