Welcome to dbFreaks.com!
FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Annotation question

 
   Database Help (Home) -> XML RSS
Next:  SSMS 2008 - Error on registered server list  
Author Message
Scienter

External


Since: Jul 31, 2006
Posts: 5



(Msg. 1) Posted: Tue Aug 19, 2008 3:37 pm
Post subject: Annotation question
Archived from groups: microsoft>public>sqlserver>xml (more info?)

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(Cool 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(Cool 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

 >> Stay informed about: Annotation question 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
SQL/XML Annotation Question - a different take - So..... we have these files from different vendors in XML to be loaded into SQL Server. Looks like they don't have the annotation/relationship sections at the towards the top of the files. These are complicated schemas and people who were creating them...

annotation issue with SQLXML - I am getting "relationship expected on" errors on SQLXML Loading Code when I am creating the tables and then perhaps adding.inserting the data. These are small files http://www.HumanGenome.org/data/schema1.xsd http://www.HumanGenome.org/data/...

BulkLoad in multiple tables - How to write the annotations? - My Problem: I have to import XML-Files into a complex SQlServer-DB (20 Tables,m2n-relations) via XML Bulkload. The annotations in the Mapping-Scheme looks like: --- <sql:relationship name="RLS_LUN" parent="RLS" parent-key="R...

BulkLoad question - In order to upload data using SQLXML Bulkload, must the data and schema reside in a file, or can you store the data to a variable, then use Bulkload ?

XML Question - Hello I have the following XML file <?xml version="1.0" encoding="us-ascii" ?> - <clsProdDataSingleBill> - <PhoneNo> <string>0893877610</string> </PhoneNo> - <Email> <string>D...
   Database Help (Home) -> XML All times are: Pacific Time (US & Canada) (change)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]