How to import this schema and file into SQL with bulkload

Hi,
I have been given the following file that includes both the schema and the
XML data. It is about 6MB so I just show the start and the end of the file
<START SCHEMA SECTION>
<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<VFPData xml:space="preserve">
<xsd:schema id="VFPData" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:element name="VFPData" msdata:IsDataSet="true">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="lotdata" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="lh_recno" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="lh_refno" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="lh_seqno" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="lh_ltype" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="3"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="bh_stowloc" use="required">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace"
processContents="lax"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<END SCHEMA SECTION>
<DATA SECTION>
<lotdata lh_recno=" 91870" lh_refno=" 91870" lh_seqno="230 "
lh_ltype="CLP" lh_code=" 4993" lh_sale="M33 " lh_season="04"
lh_schdate="2005-02-17" lh_sroom="ROOM2 " lh_csect="ODD "
lh_gsect="ODDS" lh_catflg="false" lh_source=" " lh_aucent=" "
lh_chgdate="2005-02-15T08:44:57" lh_catpage="298" lh_porg="ESMQ "
lh_selorg="ESMQ " lh_relorg="AWLQ " lh_storg="AWLQ " lh_stcent="Q "
lh_sdate=" - - " lh_sldate=" - - T : : " lh_sorg=" "
lh_psorg=" " lh_qsch1=" " lh_qsch2=" "
lh_catsym="BN " lh_wstate="GRSY " lh_grsyflg="true" lh_packin="STD "
lh_bales="3" lh_tbales="3" lh_rnetkg="465.00" lh_rtare="6.00"
lh_pprice="0.0" lh_pbasis=" " lh_pclnkg="0.00" lh_pybasis=" "
lh_pcurr=" " lh_pcurate="0.000000" lh_sprice="0.0" lh_sbasis=" "
lh_sclnkg="0.00" lh_rprice="0.0" lh_rbasis=" " lh_saleout=" "
lh_verify=" " lh_ppsch="20.10" lh_pcalbas="$/BALE" lh_wtx="PAID " lh_comm="
" lh_intcomm=" " lh_tsycodc=" " lh_typec="
" lh_typflgc="false" lh_tsycod1="IDS" lh_type1="XLF5E.40
" lh_typflg1="false" lh_tsycod2=" " lh_type2="
" lh_typflg2="false" lh_tsycod3=" " lh_type3="
" lh_typflg3="false" lh_tsycod4=" " lh_type4="
" lh_typflg4="false" lh_typeq=" "
lh_fghtchg="0.00" lh_fghtrbt="5.96" lh_delbas="DND " lh_delcent=" "
lh_scode=" " ct_netkg="465.00" ct_sampkg="0.00" ct_type1="PTC "
ct_prefx1="3" ct_code1="07454266" ct_suffx1="B" ct_chk1="7" ct_lab1="AWTA "
ct_wbase="62.39" ct_mic="24.3" ct_mictype=" " ct_afmic="0.0" ct_lsmic="24.3"
ct_lssdm="5.6" ct_lscvm="23.0" ct_lscomft="88.2" ct_ofmic="0.0"
ct_ofsdm="0.0" ct_ofcvm="0.0" ct_ofcomft="0.0" ct_vmb="1.40" ct_hh="0.0"
ct_vm1="0.2000" ct_vm2="1.2000" ct_vm3="0.0000" ct_yld1="72.00"
ct_ytyp1="SDRY1" ct_clkg1="0.00" ct_yld2="76.40" ct_ytyp2="SCD17"
ct_clkg2="0.00" ct_yld3="73.50" ct_ytyp3="JCS " ct_clkg3="0.00"
ct_yld4="69.80" ct_ytyp4="ACARB" ct_clkg4="0.00" ct_minmic="0.0"
ct_maxmic="0.0" ct_minvmb="0.0" ct_maxvmb="0.0" ct_fctwb="0.00"
ct_fctvmb="0.00" ct_fcthh="0.00" ct_fctmic="0.000000" ct_rtst1="NORM "
ct_reiss1=" " ct_type2=" " ct_prefx2=" " ct_code2=" "
ct_suffx2=" " ct_chk2=" " ct_lab2=" " ct_slen="0" ct_slencv="0"
ct_str="0" ct_minlen="0" ct_maxlen="0" ct_minstr="0" ct_maxstr="0"
ct_pobt="0" ct_pobm="0" ct_pobb="0" ct_haut="0" ct_ehaut="0.0" ct_lstype="
" ct_lsecur="false" ct_type3=" " ct_prefx3=" " ct_code3=" "
ct_suffx3=" " ct_chk3=" " ct_lab3=" " ct_colx="0.0" ct_coly="0.0"
ct_colz="0.0" ct_ecolyz="0.0" ct_colyz="0.0" ct_mincolx="0.0"
ct_maxcolx="0.0" ct_mincoly="0.0" ct_maxcoly="0.0" ct_mincolz="0.0"
ct_maxcolz="0.0" ct_minclyz="0.0" ct_maxclyz="0.0" ct_reiss3=" "
ct_cvh="0.0" ct_noil="0.0" ct_dark="N" bh_brandp="SR/COR
" bh_descp="AAALM " bh_area="N28 " bh_stowloc="
"/>
</VFPData>
<END DATA SECTION>
As you can see from the data, I have left out lots of fields to fit in post.
Anyway, we want to do a bulk load of this data, but none of the schema
sections look like examples on the web where you can setup 'sql:' options
for the field names etc.. In fact this XML/schema file don't look like
anything I have seen on the web. I guess it is just a bit more complex than
usual.
So would I be able do a bulkload with this data?
I have cut out the schema and pasted into another file but I don't know
where to go next as the file looks so different from others I have seen.
Can anyone help me with some options on how to use these files for a bulk
load?
TIA
NathanHi Nathan,
my suggestion:
DId you design a database (inclusive tables and relations) already? If NOT,
do this...
(maybe with the help of XMLSpy ... export xml data to database...)
then
Take your XML Datafile, open it with XMLSpy (Enterprise Edition in
evaluation version available), and go through:
--> Convert --> Create XML schema from DB structure
Now you have mapping-schema. ToDo: Add the missing relationships
like...
<xs:annotation>
<xs:appinfo>
<sql:relationship name="name"
parent="Table" parent-key="Table_ID"
child="RelatedTable" child-key="Table_ID"/>
</xs:appinfo>
</xs:annotation>
...
then, add the sql:is-constant for your root-element like ...
<xs:element name="ROOT" sql:is-constant="1">
and your mapping schema is complete.
Regards
SUsanne
"Nathan Simpson" wrote:

> Hi,
> I have been given the following file that includes both the schema and the
> XML data. It is about 6MB so I just show the start and the end of the file
> <START SCHEMA SECTION>
> <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
> <VFPData xml:space="preserve">
> <xsd:schema id="VFPData" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
> <xsd:element name="VFPData" msdata:IsDataSet="true">
> <xsd:complexType>
> <xsd:choice maxOccurs="unbounded">
> <xsd:element name="lotdata" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="lh_recno" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="8"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="lh_refno" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="8"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="lh_seqno" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="8"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="lh_ltype" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="3"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> <xsd:attribute name="bh_stowloc" use="required">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="20"/>
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace"
> processContents="lax"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> <END SCHEMA SECTION>
> <DATA SECTION>
> <lotdata lh_recno=" 91870" lh_refno=" 91870" lh_seqno="230 "
> lh_ltype="CLP" lh_code=" 4993" lh_sale="M33 " lh_season="04"
> lh_schdate="2005-02-17" lh_sroom="ROOM2 " lh_csect="ODD "
> lh_gsect="ODDS" lh_catflg="false" lh_source=" " lh_aucent=" "
> lh_chgdate="2005-02-15T08:44:57" lh_catpage="298" lh_porg="ESMQ "
> lh_selorg="ESMQ " lh_relorg="AWLQ " lh_storg="AWLQ " lh_stcent="Q "
> lh_sdate=" - - " lh_sldate=" - - T : : " lh_sorg=" "
> lh_psorg=" " lh_qsch1=" " lh_qsch2=" "
> lh_catsym="BN " lh_wstate="GRSY " lh_grsyflg="true" lh_packin="STD "
> lh_bales="3" lh_tbales="3" lh_rnetkg="465.00" lh_rtare="6.00"
> lh_pprice="0.0" lh_pbasis=" " lh_pclnkg="0.00" lh_pybasis=" "
> lh_pcurr=" " lh_pcurate="0.000000" lh_sprice="0.0" lh_sbasis=" "
> lh_sclnkg="0.00" lh_rprice="0.0" lh_rbasis=" " lh_saleout=" "
> lh_verify=" " lh_ppsch="20.10" lh_pcalbas="$/BALE" lh_wtx="PAID " lh_comm
="
> " lh_intcomm=" " lh_tsycodc=" " lh_typec="
> " lh_typflgc="false" lh_tsycod1="IDS" lh_type1="XLF5E.40
> " lh_typflg1="false" lh_tsycod2=" " lh_type2="
> " lh_typflg2="false" lh_tsycod3=" " lh_type3="
> " lh_typflg3="false" lh_tsycod4=" " lh_type4="
> " lh_typflg4="false" lh_typeq=" "
> lh_fghtchg="0.00" lh_fghtrbt="5.96" lh_delbas="DND " lh_delcent=" "
> lh_scode=" " ct_netkg="465.00" ct_sampkg="0.00" ct_type1="PTC
"
> ct_prefx1="3" ct_code1="07454266" ct_suffx1="B" ct_chk1="7" ct_lab1="AWTA
"
> ct_wbase="62.39" ct_mic="24.3" ct_mictype=" " ct_afmic="0.0" ct_lsmic="24.
3"
> ct_lssdm="5.6" ct_lscvm="23.0" ct_lscomft="88.2" ct_ofmic="0.0"
> ct_ofsdm="0.0" ct_ofcvm="0.0" ct_ofcomft="0.0" ct_vmb="1.40" ct_hh="0.0"
> ct_vm1="0.2000" ct_vm2="1.2000" ct_vm3="0.0000" ct_yld1="72.00"
> ct_ytyp1="SDRY1" ct_clkg1="0.00" ct_yld2="76.40" ct_ytyp2="SCD17"
> ct_clkg2="0.00" ct_yld3="73.50" ct_ytyp3="JCS " ct_clkg3="0.00"
> ct_yld4="69.80" ct_ytyp4="ACARB" ct_clkg4="0.00" ct_minmic="0.0"
> ct_maxmic="0.0" ct_minvmb="0.0" ct_maxvmb="0.0" ct_fctwb="0.00"
> ct_fctvmb="0.00" ct_fcthh="0.00" ct_fctmic="0.000000" ct_rtst1="NORM "
> ct_reiss1=" " ct_type2=" " ct_prefx2=" " ct_code2=" "
> ct_suffx2=" " ct_chk2=" " ct_lab2=" " ct_slen="0" ct_slencv="0"
> ct_str="0" ct_minlen="0" ct_maxlen="0" ct_minstr="0" ct_maxstr="0"
> ct_pobt="0" ct_pobm="0" ct_pobb="0" ct_haut="0" ct_ehaut="0.0" ct_lstype="
> " ct_lsecur="false" ct_type3=" " ct_prefx3=" " ct_code3="
"
> ct_suffx3=" " ct_chk3=" " ct_lab3=" " ct_colx="0.0" ct_coly="0.0"
> ct_colz="0.0" ct_ecolyz="0.0" ct_colyz="0.0" ct_mincolx="0.0"
> ct_maxcolx="0.0" ct_mincoly="0.0" ct_maxcoly="0.0" ct_mincolz="0.0"
> ct_maxcolz="0.0" ct_minclyz="0.0" ct_maxclyz="0.0" ct_reiss3=" "
> ct_cvh="0.0" ct_noil="0.0" ct_dark="N" bh_brandp="SR/COR
> " bh_descp="AAALM " bh_area="N28 " bh_stowloc="
> "/>
> </VFPData>
> <END DATA SECTION>
> As you can see from the data, I have left out lots of fields to fit in pos
t.
> Anyway, we want to do a bulk load of this data, but none of the schema
> sections look like examples on the web where you can setup 'sql:' options
> for the field names etc.. In fact this XML/schema file don't look like
> anything I have seen on the web. I guess it is just a bit more complex tha
n
> usual.
> So would I be able do a bulkload with this data?
> I have cut out the schema and pasted into another file but I don't know
> where to go next as the file looks so different from others I have seen.
> Can anyone help me with some options on how to use these files for a bulk
> load?
> TIA
> Nathan
>
>|||Sorry, you don't have to open your XML datafile...
You can create the schema from your database without it
"susanne" wrote:
> Hi Nathan,
> my suggestion:
> DId you design a database (inclusive tables and relations) already? If NOT
,
> do this...
> (maybe with the help of XMLSpy ... export xml data to database...)
> then
> Take your XML Datafile, open it with XMLSpy (Enterprise Edition in
> evaluation version available), and go through:
> --> Convert --> Create XML schema from DB structure
> Now you have mapping-schema. ToDo: Add the missing relationships
> like...
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="name"
> parent="Table" parent-key="Table_ID"
> child="RelatedTable" child-key="Table_ID"/>
> </xs:appinfo>
> </xs:annotation>
> ...
> then, add the sql:is-constant for your root-element like ...
> <xs:element name="ROOT" sql:is-constant="1">
> and your mapping schema is complete.
> Regards
> SUsanne
>
> "Nathan Simpson" wrote:
>