Fun with static XQuery evaluation – 3

After the last two entries, you might be thinking "I guess I can never use text() as a node test with typed XML again". Not so. The error message reads: 'text()' is not supported on simple typed or 'http://www.w3.org/2001/XMLSchema#anyType' elements. So what's left? Mixed content, for one thing. Mixed content consists of a mixture of text and also embedded subelements.

If we change the schema to allow mixed content (this schema also allows a particular subelement):

CREATE XML SCHEMA COLLECTION mixedage AS
'<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="urn:ages"
xmlns:tns="urn:ages">
  <xs:complexType name="age" mixed="true">
    <xs:complexContent mixed="true">
      <xs:restriction base="xs:anyType">
         <xs:sequence>
           <xs:element name="dogyears" type="xs:int"/>
         </xs:sequence>
      </xs:restriction>
    </xs:complexContent>
  </xs:complexType>

<xs:element name="age" type="tns:age"/>
</xs:schema>
'

Then the text() node test works with typed XML just fine:

DECLARE @x xml(mixedage)
SET @x = '
<ag:age xmlns:ag="urn:ages">This is the age in dog years<dogyears>3</dogyears></ag:age>'
— now it works OK
SELECT @x.query('
declare default namespace "urn:ages";
/age/text()')
GO

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.