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