Hi,
I was wondering if anybody could assist me with annotated XSDs when
attempting to bulkload XML.
Here's the structure of the xml I'm receiving and attempting to load into 2
tables.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Envelope xmlns=http://www.xxxx.com/schema/billofmaterials
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Plant>ZZ01</Plant>
<TransType>BOMMAT04</TransType>
<BPID>0000000000396343</BPID>
<Transaction>
<BOMNumber>00007749</BOMNumber>
<Assembly>CTO00006</Assembly>
<ValidFrom>20080717</ValidFrom>
<ChangeNumber/>
<Components>
<Component>CTO00012</Component>
<ItemNumber>0010</ItemNumber>
<Quantity>1</Quantity>
<UnitOfMeasure>EA</UnitOfMeasure>
</Components>
<Components>
<Component>CTO00009</Component>
<ItemNumber>0030</ItemNumber>
<Quantity>1</Quantity>
<UnitOfMeasure>EA</UnitOfMeasure>
</Components>
</Transaction>
</Envelope>
My insertion tables are as follows:
dbo.TempBomHeader (MessageNumber nvarchar(20) not null,
BOMNumber nvarchar(12) not null,
Plant nvarchar(4) not null,
BOMMaterial nvarchar(25) not null,
EffectiveFrom nvarchar(

not null,
EngChangeNumber nvarchar(10) not null,
Valid bit not null)
dbo.TempBOMItems (BOMNumber nvarchar(12) not null,
Component nvarchar(25) not null,
ItemNumber int not null,
Quantity int not null,
UOM nvarchar(

not null,
Usage nvarchar(10) not null)
My annotated xsd schema is defined as follows:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns=http://www.xxxx.com/schema/billofmaterials
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
targetNamespace=http://www.xxxx.com/schema/billofmaterials
elementFormDefault="qualified">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BOMNumberKey" parent="TempBOMHeader"
parent-key="BOMNumber" child="TempBOMItems" child-key="BOMNumber"/>
</xs:appinfo>
</xs:annotation>
<xs:simpleType name="plantcode">
<xs:annotation>
<xs:documentation>WERKS</xs:documentation>
</xs:annotation>
<xs:restriction base="xs:normalizedString">
<xs:maxLength value="4"/>
<xs:minLength value="1"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="material">
<xs:annotation>
<xs:documentation>MATNR</xs:documentation>
</xs:annotation>
<xs:restriction base="xs:normalizedString">
<xs:maxLength value="25"/>
<xs:minLength value="1"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="sapdate">
<xs:annotation>
<xs:documentation>SAP date in YYYYMMDD format</xs:documentation>
</xs:annotation>
<xs:restriction base="xs:normalizedString">
<xs:minLength value="8"/>
<xs:maxLength value="8"/>
</xs:restriction>
</xs:simpleType>
<xs:element name="Envelope" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Plant" type="plantcode" sql:relation="TempBOMHeader"
sql:field="Plant" sql:datatype="nvarchar"/>
<xs:element name="BPID" type="xs:string" sql:relation="TempBOMHeader"
sql:field="MessageNumber" sql:datatype="nvarchar"/>
<xs:element name="Transaction" maxOccurs="unbounded"
sql:relation="TempBOMHeader">
<xs:complexType>
<xs:sequence>
<xs:element name="BOMNumber" type="xs:normalizedString"
sql:relation="TempBOMHeader" sql:field="BOMNumber" sql:datatype="nvarchar"/>
<xs:element name="Assembly" type="material"
sql:relation="TempBOMHeader" sql:field="BOMMaterial"
sql:datatype="nvarchar"/>
<xs:element name="ValidFrom" type="sapdate"
sql:relation="TempBOMHeader" sql:field="EffectiveFrom"
sql:datatype="nvarchar"/>
<xs:element name="ChangeNumber" type="xs:normalizedString"
sql:relation="TempBOMHeader" sql:field="EngChangeNumber"
sql:datatype="nvarchar"/>
<xs:element name="Components" maxOccurs="unbounded"
sql:relation="TempBOMItems" sql:relationship="BOMNumberKey">
<xs:complexType>
<xs:sequence>
<xs:element name="Component" type="material"
sql:relation="TempBOMItems" sql:field="Component" sql:datatype="nvarchar"/>
<xs:element name="ItemNumber" type="xs:normalizedString"
sql:relation="TempBOMItems" sql:field="ItemNumber" sql:datatype="int"/>
<xs:element name="Quantity" type="xs:positiveInteger"
sql:relation="TempBOMItems" sql:field="Quantity" sql:datatype="int"/>
<xs:element name="UnitOfMeasure" type="xs:normalizedString"
sql:relation="TempBOMItems" sql:field="UOM" sql:datatype="nvarchar"/>
<xs:element name="Usage" type="xs:normalizedString" default="100"
nillable="0" minOccurs="0" sql:relation="TempBOMItems" sql:field="Usage"
sql:datatype="nvarchar"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
When I fire up bulkload, I end up with the following records in
TempBOMHeader:
MessageNumber BOMNumber Plant BOMMaterial EffectiveFrom
EngChangeNumber
NULL NULL ZZ01 NULL
NULL NULL
0000000000396343 NULL NULL NULL NULL
NULL
NULL 00007749 NULL CTO00006 20080717
NULL
It appears to have interpreted my mapping for the elements <BPID>, <Plant>,
<Transaction> as 3 separate records.
What I actually want is one record that looks like the one below:
MessageNumber BOMNumber Plant BOMMaterial EffectiveFrom
EngChangeNumber
0000000000396343 00007749 ZZ01 CTO00006 20080717
NULL
Is there a way for me to achieve this kind of mapping without asking the
source of this xml to change its structure?
The insert to TempBOMItems is correct though.
Thanks in advance,
Tristan