Question Creating schema for bulk load of XML files. Please help.

Clint79

Member
Joined
Apr 13, 2010
Messages
7
Programming Experience
Beginner
Hi guys,

I have a project which needs to be completed by Tuesday but I have hit a brick wall with the XML schema side. I am very new to XML and Schemas so this will seem very simple to a lot of you experts but I have been trying to get it going for two days and am running out of time. I have gained alot of knowledge about XML and schemas over the last 2 days but apparently not enough.
I am trying to use the XML bulk load function to insert the values and table structure from a XML document recieved from a Linux based device. When I try I get the error "Schema: relationship expected on 'table'."
I have been trying to figure out how to put the SQL relationships in but have had no luck.
A dumbed down version of the XML document is below...

HTML:
<?xml version="1.0" encoding="UTF-8" ?> 
<MyTables>
 <table name="TABLE1">
  <record>
   <field column_name="ID">1</field> 
   <field column_name="DESC">Hello world</field> 
  </record>
  <record>
   <field column_name="ID">2</field> 
   <field column_name="DESC">Thank you</field> 
  </record>
 </table>
 <table name="TABLE2">
  <record>
   <field column_name="ID">1</field> 
   <field column_name="F_NAME">David</field> 
   <field column_name="L_NAME">Smith</field> 
  </record>
  <record>
   <field column_name="ID">2</field> 
   <field column_name="F_NAME">Jane</field> 
   <field column_name="L_NAME">McDonnald</field> 
  </record>
 </table>
</MyTables>
The schema I have is as follows...

HTML:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<xs:element name="tables">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="table" minOccurs="0" maxOccurs="unbounded">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="item" minOccurs="0" maxOccurs="unbounded">
								<xs:complexType>
									<xs:sequence>
										<xs:element name="field" minOccurs="0" maxOccurs="unbounded">
											<xs:complexType>
												<xs:simpleContent>
													<xs:extension base="xs:string">
														<xs:attribute name="name" use="required">
															<xs:simpleType>
																<xs:restriction base="xs:string"/>
															</xs:simpleType>
														</xs:attribute>
													</xs:extension>
												</xs:simpleContent>
											</xs:complexType>
										</xs:element>
									</xs:sequence>
									<xs:attribute name="scale_plu_number" use="optional">
										<xs:simpleType>
											<xs:restriction base="xs:integer"/>
										</xs:simpleType>
									</xs:attribute>
									<xs:attribute name="name" use="required">
										<xs:simpleType>
											<xs:restriction base="xs:string"/>
										</xs:simpleType>
									</xs:attribute>
									<xs:attribute name="ordering" use="optional">
										<xs:simpleType>
											<xs:restriction base="xs:string"/>
										</xs:simpleType>
									</xs:attribute>
									<xs:attribute name="id" use="required">
										<xs:simpleType>
											<xs:restriction base="xs:string"/>
										</xs:simpleType>
									</xs:attribute>
									<xs:attribute name="department" type="xs:integer" use="optional"/>
								</xs:complexType>
							</xs:element>
						</xs:sequence>
						<xs:attribute name="name" use="required">
							<xs:simpleType>
								<xs:restriction base="xs:string"/>
							</xs:simpleType>
						</xs:attribute>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>


The VB code I am using for the XML bulk load is as follows (this code should create the database tables if they do not exist)...

Code:
Dim objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.Transaction = True
objBulkLoad.ConnectionString = "provider=SQLOLEDB;Data Source=.\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"
objBulkLoad.ErrorLogFile = "error.log"
objBulkLoad.KeepIdentity = False
objBulkLoad.SchemaGen = True
objBulkLoad.Execute("C:\temp\Tables.xsd", "C:\temp\XMLDataFile.xml")
I believe I need to setup the SQL relations and have been trying to but have not had success. I do not need to do anything fancy, just get the data loading into the database so reports can be generated from the data.

Any healp would be greatly appreciated.

Thanks.
 

Clint79

Member
Joined
Apr 13, 2010
Messages
7
Programming Experience
Beginner
No help yet...

Well I have had a sleep and back to it. This is what I have been trying...

Below is my test xml document.
HTML:
<totals>
 <table name="Table1">
  <record>
    <CustomerId>1111</CustomerId>
    <CompanyName>Sean Chai</CompanyName>
    <City>NY</City>
  </record>
  <record>
    <CustomerId>1112</CustomerId>
    <CompanyName>Tom Johnston</CompanyName>
    <City>LA</City>
  </record>
  <record>
    <CustomerId>1113</CustomerId>
    <CompanyName>Institute of Art</CompanyName>
  </record>
 </table>
 <table name="Table2">
  <record>
    <CustomerId>1</CustomerId>
    <CompanyName>Anna Simpson</CompanyName>
    <City>NY</City>
  </record>
  <record>
    <CustomerId>2</CustomerId>
    <CompanyName>Tim Ericson</CompanyName>
    <City>LA</City>
  </record>
  <record>
    <CustomerId>3</CustomerId>
    <CompanyName>Bob Robertson</CompanyName>
  </record>
 </table>
</totals>
Using the below schema
HTML:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data" 
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" > 

   <ElementType name="CustomerId" dt:type="int" />
   <ElementType name="CompanyName" dt:type="string" />
   <ElementType name="City" dt:type="string" />

   <ElementType name="table" sql:is-constant="1">
         <element type="record" />
   </ElementType>

   <ElementType name="record"  sql:relation="Customer">
      <element type="CustomerId"  sql:field="CustomerId" />
      <element type="CompanyName" sql:field="CompanyName" />
      <element type="City"        sql:field="City" />
   </ElementType>
</Schema>
This works but I can not figure out how to define that element of type table has the table name. No mater what I do I can not figure out how to seperate the 6 records into 2 diferent tables.

Can anyone help me with this?
 
Top Bottom