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...
The schema I have is as follows...
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)...
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.
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)...
VB.NET:
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.