Odd results reading attributes when schema applied

  • andycadley

    SSCertifiable

    Points: 5291

    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.
  • Eirikur Eiriksson

    SSC Guru

    Points: 182352

    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: 1688

    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: 5291

    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