I want to dynamically create tables in a SQL Server 2000 database that
are modelled after XML schemas. I will not know what the schemas
might look like until the documents arrive.
For example, I'd like to convert this schema:
<xs:schema
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
<xs:element name="OrgLevel">
<xs:complexType>
<xs:sequence>
<xs:element name="code" type="integer" />
<xs:element name="desc" type="string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
into this SQL command:
CREATE TABLE OrgLevel (code Int, desc varchar)
The problem is, when I walk the schema and read the name and type of
those elements, I get the correct names ("code", "desc"), but
messed-up XML-ized datatypes, like System.Xml.Schema.Datatype_int and
System.Xml.Schema.Datatype_string. How can I best determine the
equivalent SQL Server datatypes so I can build the commands to create
these tables effectively?