Odd results reading attributes when schema applied
- 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. -
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.
-
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?!?! -
Interesting, you learn something every day. Thanks chaps.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply