XML Schema Collection breaks xquery



The script below can be used to show an example of the problem I have. The script will return an error "There is no element named 'this'".


If I change the declaration of the @XML variable so it is untyped XML (replace the line DECLARE @XML XML(Test) with DECLARE @XML XML), I do not get the error, but get the expected result instead.


Why do I get the error when using the Schema collection and how can I avoid it - I want to fill a table column with properly typed xml, but I'm having trouble getting my data back out of the column.



IF EXISTS (SELECT name FROM sys.xml_schema_collections WHERE name = 'test') DROP XML SCHEMA COLLECTION Test
CREATE XML SCHEMA COLLECTION Test AS
'<xsd:schema xmlns:xsd="http://ift.tt/tphNwY"
xmlns:t="http://ift.tt/1GTErDZ"
targetNamespace="http://ift.tt/1GTErDZ"
elementFormDefault="qualified">
<xsd:element name ="this">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="What" minOccurs="1" maxOccurs="1" >
<xsd:simpleType>
<xsd:restriction base ="xsd:string">
<xsd:enumeration value ="01."/>
<xsd:enumeration value ="02."/>
<xsd:enumeration value ="03."/>
<xsd:enumeration value ="04."/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="When" minOccurs="1" maxOccurs="1" >
<xsd:simpleType>
<xsd:restriction base ="xsd:string">
<xsd:enumeration value ="01."/>
<xsd:enumeration value ="02."/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="Outcome" minOccurs="1" maxOccurs="1" >
<xsd:simpleType>
<xsd:restriction base ="xsd:string">
<xsd:enumeration value ="one"/>
<xsd:enumeration value ="two"/>
<xsd:enumeration value ="whatever"/>
<xsd:enumeration value =""/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO

DECLARE @XML XML(Test)

SET @XML =
'<this xmlns="http://ift.tt/1GTErDZ">
<What>02.</What>
<When>01.</When>
<Outcome></Outcome>
</this>'

SELECT @XML

SELECT
Field.value('What[1]','VARCHAR(100)')
FROM @Xml.nodes('/this') AS this(Field)

No comments:

Post a Comment