Automate creating SQL tables from XSD

Question

1.44/5 (2 votes)

See more:

I have a few XSD files and associated XML files containing data from a vendor. I will be importing the XML data into SQL Server. But first I need to setup the tables in SQL Server to match the structure described in the XSD files. The question I have, is there a tool or way to automate created the SQL tables? I have searched for XSD to DML, XML to SQL tools and have found anything helpful. Has anyone else done this? Any suggestions on options to try?

Posted 5-Dec-13 8:01am

mgoad99

Updated 11-Aug-19 23:41pm

Add a Solution

3 solutions

Solution 2

XML

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<xs:element name="IR56B">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="Section">  <!-- Section -->
					<xs:simpleType>
						<xs:restriction base="xs:string">
							<xs:length value="3" />
							<xs:pattern value="[0-9a-zA-Z]*" />
						</xs:restriction>
					</xs:simpleType>
				</xs:element>
				<xs:element name="ERN">  <!-- ERN -->
					<xs:simpleType>
						<xs:restriction base="xs:string">
							<xs:minLength value="1" />
							<xs:maxLength value="8" />
							<xs:pattern value="[0-9]*" />
						</xs:restriction>
					</xs:simpleType>
				</xs:element>
				<xs:element name="YrErReturn">  <!-- Year of Employer’s Return -->
					<xs:simpleType>
						<xs:restriction base="xs:string">
							<xs:length value="4" />
							<xs:pattern value="[0-9]*" />
						</xs:restriction>
					</xs:simpleType>
				</xs:element>
				<xs:element name="SubDate">  <!-- Submission Date -->
					<xs:simpleType>
						<xs:restriction base="xs:string">
							<xs:length value="8" />
							<xs:pattern value="[0-9]*" />
						</xs:restriction>
					</xs:simpleType>
				</xs:element>
				<xs:element name="ErName">  <!-- Employer’s Name -->
					<xs:simpleType>
						<xs:restriction base="xs:string">
							<xs:minLength value="1" />
							<xs:maxLength value="70" />
						</xs:restriction>
					</xs:simpleType>
				</xs:element>
				<xs:element name="Designation">  <!-- Designation -->
					<xs:simpleType>
						<xs:restriction base="xs:string">
							<xs:maxLength value="25" />
						</xs:restriction>
					</xs:simpleType>
				</xs:element>
				<xs:element name="NoRecordBatch">  <!-- No. of Records in Batch -->
					<xs:simpleType>
						<xs:restriction base="xs:string">
							<xs:minLength value="1" />
							<xs:maxLength value="5" />
							<xs:pattern value="[0-9]*" />
						</xs:restriction>
					</xs:simpleType>
				</xs:element>
				<xs:element name="TotIncomeBatch">  <!-- Total Income in Batch -->
					<xs:simpleType>
						<xs:restriction base="xs:string">
							<xs:minLength value="1" />
							<xs:maxLength value="11" />
							<xs:pattern value="[0-9]*" />
						</xs:restriction>
					</xs:simpleType>
				</xs:element>
				<xs:element name="Employee" minOccurs="1" maxOccurs="unbounded">  <!-- Employee’s IR56B record -->
					<xs:complexType>
						<xs:sequence>
							<xs:element name="SheetNo">  <!-- Sheet No -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:minLength value="1" />
										<xs:maxLength value="6" />
										<xs:pattern value="[0-9]*" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="HKID" default="AA000000A">  <!-- Employee’s HKID with Check Digit -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="9" />
										<xs:pattern value="[A-Z a-z]{0,1}[A-Za-z]{1}[0-9]{6}[Aa0-9]{0,1}" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="TypeOfForm">  <!-- Status -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:enumeration value="O" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="Surname">  <!-- Employee’s Surname -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:minLength value="1" />
										<xs:maxLength value="20" />
										<xs:pattern value="[A-Za-z'\.\-]*" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="GivenName">  <!-- Employee’s Given Names in Full -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:minLength value="1" />
										<xs:maxLength value="55" />
										<xs:pattern value="[A-Z a-z0-9!#$%&amp;\*\(\)_\+\-=\\:&quot;;&apos;&lt;&gt;?,\./@]*" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="NameInChinese">  <!-- Employee’s Full Name in Chinese -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="25" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="Sex">  <!-- Employee’s Sex -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:enumeration value="M" />
										<xs:enumeration value="F" />
										<xs:enumeration value="m" />
										<xs:enumeration value="f" />
										<xs:enumeration value="" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="MaritalStatus">  <!-- Employee’s Marital Status -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:enumeration value="1" />
										<xs:enumeration value="2" />
										<xs:enumeration value="" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PpNum">  <!-- Employee’s Passport No. and Country of Issue -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="40" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="SpouseName">  <!-- Spouse’s Name -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="50" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="SpouseHKID">  <!-- Spouse’s HKID with Check Digit -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="9" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="SpousePpNum">  <!-- Spouse’s Passport No. and Country of Issue -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="40" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="ResAddr">  <!-- Employee’s Residential Address -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="90" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AreaCodeResAddr">  <!-- Area Code of Employee’s Residential Address -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="1" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PosAddr">  <!-- Employee’s Postal Address -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="60" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="Capacity">  <!-- Capacity in which Employed -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="40" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PtPrinEmp">  <!-- If Part Time, Name of Principal Employer -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="30" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="StartDateOfEmp">  <!-- Start Date of Employment -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:length value="8" />
										<xs:pattern value="[0-9]*" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="EndDateOfEmp">  <!-- End Date of Employment -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:length value="8" />
										<xs:pattern value="[0-9]*" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PerOfSalary">  <!-- Period of Salary/Wages -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfSalary" type="amount" />  <!-- Amount of Salary/Wages -->
							<xs:element name="PerOfLeavePay">  <!-- Period of Leave Pay -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfLeavePay" type="amount" />  <!-- Amount of Leave Pay -->
							<xs:element name="PerOfDirectorFee">  <!-- Period of Director’s Fee -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfDirectorFee" type="amount" />  <!-- Amount of Director’s Fee -->
							<xs:element name="PerOfCommFee">  <!-- Period of Commission /Fees -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfCommFee" type="amount" />  <!-- Amount of Commission /Fees -->
							<xs:element name="PerOfBonus">  <!-- Period of Bonus -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfBonus" type="amount" />  <!-- Amount of Bonus -->
							<xs:element name="PerOfBpEtc">  <!-- Period of Back Pay, Payment in Lieu of Notice, Terminal Awards or 
									Gratuities, etc. -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfBpEtc" type="amount" />  <!-- Amount of Back Pay, Payment in Lieu of Notice, Terminal Awards or 
								Gratuities, etc. -->
							<xs:element name="PerOfPayRetire">  <!-- Period of Certain Payments from Retirement Schemes -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfPayRetire" type="amount" />  <!-- Amount of Certain Payments from Retirement Schemes -->
							<xs:element name="PerOfSalTaxPaid">  <!-- Period of Salaries Tax Paid by Employer -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfSalTaxPaid" type="amount" />  <!-- Amount of Salaries Tax Paid by Employer -->
							<xs:element name="PerOfEduBen">  <!-- Period of Education Benefits -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfEduBen" type="amount" />  <!-- Amount of Education Benefits -->
							<xs:element name="PerOfGainShareOption">  <!-- Period of Gain Realized Under Share Option Scheme -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfGainShareOption" type="amount" />  <!-- Amount of Gain Realized Under Share Option Scheme -->
							<xs:element name="NatureOtherRAP1">  <!-- Nature of 1st Other Rewards, Allowances or Perquisites -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="35" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PerOfOtherRAP1">  <!-- Period of 1st Other Rewards, Allowances or Perquisites -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfOtherRAP1" type="amount" />  <!-- Amount of 1st Other Rewards, Allowances or Perquisites -->
							<xs:element name="NatureOtherRAP2">  <!-- Nature of 2nd Other Rewards, Allowances or Perquisites -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="35" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PerOfOtherRAP2">  <!-- Period of 2nd Other Rewards, Allowances or Perquisites -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfOtherRAP2" type="amount" />  <!-- Amount of 2nd Other Rewards, Allowances or Perquisites -->
							<xs:element name="NatureOtherRAP3">  <!-- Nature of 3rd Other Rewards, Allowances or Perquisites -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="35" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PerOfOtherRAP3">  <!-- Period of 3rd Other Rewards, Allowances or Perquisites -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfOtherRAP3" type="amount" />  <!-- Amount of 3rd Other Rewards, Allowances or Perquisites -->
							<xs:element name="PerOfPension">  <!-- Period of Pensions -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtOfPension" type="amount" />  <!-- Amount of Pensions -->
							<xs:element name="TotalIncome">  <!-- Total Income -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:minLength value="1" />
										<xs:maxLength value="9" />
										<xs:pattern value="[0-9]*" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PlaceOfResInd">  <!-- Place of Residence Indicator -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:enumeration value="0" />
										<xs:enumeration value="1" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AddrOfPlace1">  <!-- Address of 1st Place of Residence -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="110" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="NatureOfPlace1">  <!-- Nature of 1st Place of Residence -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PerOfPlace1">  <!-- Period of 1st Place of Residence -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="26" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="RentPaidEr1" type="rentAmt" />  <!-- Rent of 1st Place of Residence Paid to Landlord by Employer -->
							<xs:element name="RentPaidEe1" type="rentAmt" />  <!-- Rent of 1st Place of Residence Paid to Landlord by Employee -->
							<xs:element name="RentRefund1" type="rentAmt" />  <!-- Rent of 1st Place of Residence Refunded to Employee -->
							<xs:element name="RentPaidErByEe1" type="rentAmt" />  <!-- Rent of 1st Place of Residence Paid to Employer by Employee -->
							<xs:element name="AddrOfPlace2">  <!-- Address of 2nd Place of Residence -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="110" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="NatureOfPlace2">  <!-- Nature of 2nd Place of Residence -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="19" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="PerOfPlace2">  <!-- Period of 2nd Place of Residence -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="26" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="RentPaidEr2" type="rentAmt" />  <!-- Rent of 2nd Place of Residence Paid to Landlord by Employer -->
							<xs:element name="RentPaidEe2" type="rentAmt" />  <!-- Rent of 2nd Place of Residence Paid to Landlord by Employee -->
							<xs:element name="RentRefund2" type="rentAmt" />  <!-- Rent of 2nd Place of Residence Refunded to Employee -->
							<xs:element name="RentPaidErByEe2" type="rentAmt" />  <!-- Rent of 2nd Place of Residence Paid to Employer by Employee -->
							<xs:element name="OverseaIncInd">  <!-- Overseas Income Indicator -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:enumeration value="0" />
										<xs:enumeration value="1" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AmtPaidOverseaCo">  <!-- Amount Paid by Overseas Company -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="20" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="NameOfOverseaCo">  <!-- Name of Overseas Company -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="60" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="AddrOfOverseaCo">  <!-- Address of Overseas Company -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="60" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
							<xs:element name="Remarks">  <!-- Remarks -->
								<xs:simpleType>
									<xs:restriction base="xs:string">
										<xs:maxLength value="60" />
									</xs:restriction>
								</xs:simpleType>
							</xs:element>
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
	<xs:simpleType name="amount">
		<xs:restriction base="xs:string">
			<xs:maxLength value="9" />
			<xs:pattern value="[0-9]*" />
		</xs:restriction>
	</xs:simpleType>
	<xs:simpleType name="rentAmt">
		<xs:restriction base="xs:string">
			<xs:maxLength value="7" />
			<xs:pattern value="[0-9]*" />
		</xs:restriction>
	</xs:simpleType>
</xs:schema>

Posted 16-Oct-16 23:08pm

tsoibenson

Solution 4

Use XSD2DB[^] worked for me like wonder.

Posted 11-Aug-19 23:41pm

Member 14055619

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RaisKazi · Accepted Answer · 2013-12-05T08:14:00

Solution 1

Have a look at below links.

http://technet.microsoft.com/en-us/library/ms171993.aspx

http://stackoverflow.com/questions/138575/how-can-i-create-database-tables-from-xsd-files

Posted 5-Dec-13 8:14am

RaisKazi

Comments

mgoad99 5-Dec-13 16:25pm

Thanks RaisKazi, i am going to try the approach in the technect link