Why is my XML not validating in a typed XML column in SQL Server 2008?_问答_开发者

Super-short version:

I solved this problem when I was nearly finished writing the question. Answer coming shortly.

Short version:

Why is SQL Server rejecting my XML inserted into a typed XML column? The XML is generated using System.Xml.Serialization.XmlSerializer in .NET 4.

Much longer version:

I have a class that I can successfully serialize using XmlSerializer. More specifically, I can successfully serialize an array of that class using XmlSerializer.

I have an XML column in a SQL Server 2008 table. If I just use an untyped XML column, inserting the output of XmlSerializer.Serialize works fine (I'm using Entity Framework, which translates the XML column into type string). However, I would like to make the column a typed XML column instead. When I do so, no matter what I try, I cannot seem to get the XML from the serializer to validate successfully.

Here is the (mildly obfuscated) XSD I used to create the typed XML column in SQL Server:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="ArrayOfMyObject"
    targetNamespace="http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd"
    xmlns="http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
>
    <xs:complexType name="MyObject">
        <xs:all minOccurs="0" maxOccurs="1">
            <xs:element name="CD" type="xs:int" />
            <xs:element name="CI" type="xs:string" />
            <xs:element name="CT" type="xs:int" />
            <xs:element name="CY" type="xs:int" />
            <xs:element name="LD" type="xs:int" />
            <xs:element name="SomeName" type="xs:string" />
            <xs:element name="SomeNumber" type="xs:string" />
            <xs:element name="SD" type="xs:int" />
            <xs:element name="ZP" type="xs:string" />
            <xs:element name="State">
                <xs:simpleType>
                    <xs:restriction base="xs:string">
                        <xs:pattern value="[A-Z][A-Z]" />
                    </xs:restriction>
                </xs:simpleType>
            </xs:element>
        </xs:all>
    </xs:complexType>

    <xs:element name="ArrayOfMyObject">
        <xs:complexType>
            <xs:sequence minOccurs="0" maxOccurs="unbounded">
                <xs:element name="MyObject" type="MyObject" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

I created the schema collection in SQL Server with:

CREATE XML SCHEMA COLLECTION [dbo].[MySchemaCollection] AS
'<The XSD from above>'

(making the obvious replacement here). Note that I did not do:

CREATE XML SCHEMA COLLECTION [dbo].[MySchemaCollection] AS
N'<The XSD from above which now gives an error>'

because the combination of NVARCHAR type with utf-8 causes SQL Server to produce an error like:

Msg 9402, Level 16, State 1, Line 3
XML parsing: line 1, character 39, unable to switch the encoding

Now, I re-create my table with the relevant column being:

[MyXmlColumn] [xml](DOCUMENT [dbo].[MySchemaCollection]) NULL

I would expect what I am inserting to be a DOCUMENT, but I have also tried creating the column as CONTENT to no avail.

I create the XmlSerializer as

_xml = new XmlSerializer(typeof(MyObject[]));

and call the serializer with

XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
ns.Add("t", "http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd");
_xml.Serialize(serializationStream, graph, ns);

where, of course, serializationStream is the output stream (in this case, a MemoryStream), and graph is of type MyObject[].

Now, the XmlSerializer produces output such as:

  <?xml version="1.0" ?> 
  <ArrayOfMyObject xmlns:t="http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd">
  <MyObject>
  <LD>1</LD> 
  <SomeName>SOME CITY 04</SomeName> 
  <SomeNumber>04</SomeNumber> 
  </MyObject>
  <MyObject>
  <LD>1</LD> 
  <SomeName>SOME CITY 05</SomeName> 
  <SomeNumber>05</SomeNumber> 
  </MyObject>
  <MyObject>
  <LD>1</LD> 
  <SomeName>SOME CITY 07</SomeName> 
  <SomeNumber>07</SomeNumber> 
  </MyObject>
  <MyObject>
  <LD>18</LD> 
  <SomeName>BANKS-FREMONT</SomeName> 
  <SomeNumber>BF</SomeNumber> 
  </MyObject>
  <MyObject>
  <LD>18</LD> 
  <SomeName>BENNINGTON</SomeName> 
  <SomeNumber>B000</SomeNumber> 
  </MyObject>
  <MyObject>
  <LD>18</LD> 
  <SomeName>CENTER</SomeName> 
  <SomeNumber>CE</SomeNumber> 
  </MyObject>
  <MyObject>
  <LD>18</LD> 
  <SomeName>FRANKLINE MAXFIELD CITY</SomeName> 
  <SomeNumber>FR</SomeNumber> 
  </MyObject>
  <MyObject>
  <LD>18</LD> 
  <SomeName>FREDERIKA LEROY CITY OF</SomeName> 
  <SomeNumber>FD</SomeNumber> 
  </MyObject>
  <MyObject>
  <LD>18</LD> 
  <SomeName>HARLAN</SomeName> 
  <SomeNumber>HL</SomeNumber> 
  </MyObject>
  <MyObject>
  <CT>15</CT> 
  <SomeName>ATLANTIC 2 AND GROVE</SomeName> 
  <SomeNumber>AT2</SomeNumber> 
  </MyObject>
  <MyObject>
  <CT>15</CT> 
  <SomeName>BRIGHTON/MARNE/GROVE 1/P</SomeName> 
  <SomeNumber>MR</SomeNumber> 
  </MyObject>
  <MyObject>
  <CT>15</CT> 
  <SomeName>EDNA/NOBLE/PLEASANT/GRIS</SomeName> 
  <SomeNumber>GS</SomeNumber> 
  </MyObject>
  <MyObject>
  <CT>15</CT> 
  <SomeName>FRANKLIN/WIOTA/GRANT/ANI</SomeName> 
  <SomeNumber>AN</SomeNumber> 
  </MyObject>
  <MyObject>
  <CT>15</CT> 
  <SomeName>MASSENA AND CITY</SomeName> 
  <SomeNumber>MS</SomeNumber> 
  </MyObject>
  <MyObject>
  <CT>15</CT> 
  <SomeName>UNION &amp; CUMBERLAND</SomeName> 
  <SomeNumber>CU</SomeNumber> 
  </MyObject>
  <MyObject>
  <CD>3</CD> 
  <State>IA</State> 
  </MyObject>
  <MyObject>
  <CD>5</CD> 
  <State>IA</State> 
  </MyObject>
  <MyObject>
  <CT>1</CT> 
  <State>IA</State> 
  </MyObject>
  <MyObject>
  <CT>2</CT> 
  <State>IA</State> 
  </MyObject>
  <MyObject>
  <CT>5</CT> 
  <State>IA</State> 
  </MyObject>
  </ArrayOfMyObject>

When I call Context.SaveChanges, I get an exception whose inner exception message is:

XML Validation: Declaration not found for element 'ArrayOfMyObject'. Location: /*:ArrayOfMyObject[1]

I have tried calling开发者_如何学JAVA and/or creating the serializer with a number of different namespace options, and I get either the same error or a similar one (alleging either that {http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd}ArrayOfMyObject was expected but ArrayOfMyObject was found, or vice versa).

What do I need to change so that the XML from the serializer will pass XML validation on SQL Server 2008?

I went ahead and posted this question hoping that the answer could later help someone, even though I figured out the answer when I was actually trying to reproduce one of the "similar" error messages from XML validation.

There may be other ways to get this to work, but what ended up working for me was:

Change the xs:schema element in the XSD by changing xmlns to xmlns:t and adding elementFormDefault="qualified" to get:

<xs:schema id="ArrayOfMyObject"
    targetNamespace="http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd"
    xmlns:t="http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    elementFormDefault="qualified">

Add minOccurs="0" to each of the xs:element tags in the MyObject type. I misunderstood this reference document to mean that using xs:all with minOccurs="0" automatically applied to all xs:element tags within. If that is supposed to be correct, SQL Server does not seem to recognize it as such. So, to pass validation, I had to make each element similar to

<xs:element name="CD" type="xs:int" minOccurs="0" />

since not all the tags will be present within each MyObject element.

Add the namespace prefix t to the type of MyObject within the ArrayOfMyObject element:

<xs:element name="ArrayOfMyObject">
    <xs:complexType>
        <xs:sequence minOccurs="0" maxOccurs="unbounded">
            <xs:element name="MyObject" type="t:MyObject" />
        </xs:sequence>
    </xs:complexType>
</xs:element>

When creating my XmlSerializer, use both the type and default namespace, both for serialization and deserialization:

_xml = new XmlSerializer(typeof(MyObject[]), "http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd");

I had tried this before, but now I know it is part of the final solution.

When calling XmlSerializer.Serialize, do the following:

XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
ns.Add("t", "http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd");
_xml.Serialize(serializationStream, graph, ns);

On the MyObject class itself, add the attribute

[XmlRoot(Namespace="http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd")]

On each property of MyObject included in serialization, add the attribute

[XmlElement(Namespace="http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd", Form=System.Xml.Schema.XmlSchemaForm.Qualified)]

Finally, MyObject already had custom serialization implemented using ISerializable and, upon my starting this feature, IXmlSerializable. This fact might make the attribute additions above unnecessary, but I have not tested that assertion. Either way, it was necessary in the IXmlSerializable.WriteXml(System.Xml.XmlWriter writer) method to use

writer.WriteElementString(name, "http://www.myproduct.com/2011/04/08/ArrayOfMyObject.xsd", propertyValue.ToString());

where name was the name of the property to be serialized and propertyValue was its value. It was not necessary to use the form of WriteElementString that specifies the namespace prefix; the serializer included the correct prefix automatically. The IXmlSerializable.ReadXml(System.Xml.XmlReader reader) method already used reader.LocalName to retrieve the element names, so there was no need to make any changes there to handle the namespace.

Sorry about the bullet formatting; lists seem not to play nicely with code blocks.

I hope this helps someone in a Google search sometime.