SQLXMLBulkLoad Schema Validation Issue (Easy)

Question

I have a huge XML file full of employees and information, and have a question.

Example of XML File:

<Employees>
  <Employee>
    <EmployeeID>blah</EmployeeID>
    <FirstName>blah</FirstName>
    <LastName>blah</LastName>
    <MiddleName>blah</MiddleName>
    .......... and on
  </Employee>
    ........ and on
</Employees>

My schema, so far is like this:

<?xml version="1.0" encoding="utf-8"?>

<xsd:element name="Employees" sql:relation="The_Employees">
    <xsd:complexType>
        <xsd:sequence>
            <xsd:element name="Employee">
                <xsd:element name="EmployeeID" sql:field="EmpNo" type="xsd:integer"/>
                <xsd:element name="FirstName" sql:field="FirstName">
                    <xsd:simpleType>
                        <xsd:restriction base="xsd:string">
                            <xsd:whiteSpace value="collapse"/>
                        </xsd:restriction>
                    </xsd:simpleType>
                </xsd:element>
               .......... and on
            </xsd:element>
        </xsd:sequence> 
    </xsd:complexType>
</xsd:element>

From my research, the root element is supposed to contain the relation with your table in the DB (for privacy sake, the name in this example is "The_Employees") So, I made that relation, and also made the sql:field for each column in the table because the column names are different than the XML Element tags in most cases. However, in what ways to do I relate each individual <Employee> tag to my table? Also, whilst validating the XML, it throws this error:

The content of 'Employee' must match (annotation?, (simpleType | complexType)?, (unique | key | keyref)*)). A problem was found starting at: element.

It seems like the validator is thinking that I am trying to split the content into two separate tables and therefore need to annotate that, but I am not. Any suggestions?

Just FYI: The end product here is going to be a VB.NET program which uses SQLXMLBulkLoad to load the data from the XML file into a fresh SQL Table.

score 0 · Accepted Answer · answered Jul 19 '12 at 16:31

Alright... the fix was simple and I am answering this so that someone in the future may refer to it. If you have a root element as well as each individual parent element under that root element and you are using BulkLoad, then you can set the root element to sql:is-constant = 1. This tells the schema that the root element exists, but to ignore it. Also, the above schema was ammended to:

<xsd:element name="Employees" sql:is-constant=1>
<xsd:complexType>
    <xsd:sequence>
        <xsd:element name="Employee" sql:relation="The_Employees">
          <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="EmployeeID" sql:field="EmpNo" type="xsd:integer"/>
            <xsd:element name="FirstName" sql:field="FirstName">
                <xsd:simpleType>
                    <xsd:restriction base="xsd:string">
                        <xsd:whiteSpace value="collapse"/>
                    </xsd:restriction>
                </xsd:simpleType>
            </xsd:element>
           .......... and on
          </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
    </xsd:sequence> 
</xsd:complexType>

SQLXMLBulkLoad Schema Validation Issue (Easy)

1 Answers1