by I have a bid xml file to import into SQL.
Here'S a little sample for the xml file :
<RX_SPECTACLE Id="SP173F" EngineId="SP173F"
Creation_date="2002-01-23T14:05:37" >
<HEADER>
<CUSTOMER LoginID="opticraf" Email="" >
<ACCOUNTS>
<ACCOUNT Type="RX" Type_name="RX ORDER"
Class="BIL" Reference_number="30110990120"
Name="0990120" FID="" SS="" >
<ADDRESS Street_Number="17311" Street_Name="NE
Halsey St" Suite="" Zipcode="97230" City="Portland"
State="OR" Country="US" Tel="" Extension="" />
</ACCOUNT>
<ACCOUNT Type="RX" Type_name="RX ORDER"
Class="SHP" Reference_number="30110990120"
Name="0990120" FID="" SS="" >
<ADDRESS Street_Number="17311" Street_Name="NE
Halsey St" Suite="" Zipcode="97230" City="Portland"
State="OR" Country="US" Tel="" Extension="" />
</ACCOUNT>
</ACCOUNTS>
</CUSTOMER>
<SUPPLIER Reference_number="3011"
Network_name="ABCr" Network_id="3010" Email="" >
<ADDRESS Street_Number="1750"
Street_Name="MonetaryLane" Suite="" Zipcode="7009"
City="Carrollton" State="TX" Country="US" Tel="000-000-
000" Extension="0" />
</SUPPLIER>
<DELIVERY DeliveryTo="Office" />
</HEADER>
etc...
Im using the SQL Bulk Load feature to bulk it directly
into SQL tables.
Here'S my problem, I would like to put everything in the
same table, but I can't get it to work for sub-element.
Here's what I have as a schema :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-
schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Rx_Spectacle_Account_Ref"
parent="Rx_Spectacle"
parent-key="CleVisionWeb"
child="Account"
child-key="CleVisionWeb" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="RX_SPECTACLE"
sql:relation="Rx_Spectacle"
sql:key-fields="CleVisionWeb" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="HEADER" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CUSTOMER" sql:is-
constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ACCOUNTS" sql:is-
constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ACCOUNT"
sql:relation="Account"
sql:relationship="Rx_Spectacle_Account_Ref">
<xsd:complexType>
<xsd:attribute
name="Class" sql:field="TypeCompte" type="xsd:string" />
<xsd:attribute name="Name"
sql:field="NumCompte" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="Id" sql:field="Key"
type="xsd:string" />
<xsd:attribute name="Creation_date"
sql:field="CreationDate" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
How can I add the address sub-element into the same table
(Account)?
Is it possible to do it???
I've try this (and a lot of other things...), but I
receive error :
<xsd:element name="ACCOUNT" sql:relation="Account"
sql:relationship="Rx_Spectacle_Account_Ref">
<xsd:complexType>
<xsd:element name="ADDRESS" sql:relation="Account"
sql:relationship="Rx_Spectacle_Account_Ref">
<xsd:complexType>
<xsd:attribute name="Street_Number"
sql:field="StreetNum" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
<xsd:attribute name="Class" sql:field="TypeCompte"
type="xsd:string" />
<xsd:attribute name="Name" sql:field="NumCompte"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
Please help me, or just tell me if it's possible...
For now, I had to make an SQL table for each tag, then i
have an Account table, an address table, etc...
And since its a very large file (12K character), I have
now 15 tables!!!
Cant i just put everything into the same table, but each
into its own field????