Propagation of event notification when tables are updated.

I would like to propagate an event signal to an external application
when a table in my MSSql2000 server is updated.

Prog A; I have an external application adding records to a table.
Prog B; I have another external application using this table as well.

When Prog A creates a new record in the Table, how can I have Prog B be
notified of the event without polling the table or creating a link
between Prog A and Prog B.

Thanks.> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event

Depends what you mean by "notified". If you want to invoke some code within
the current process (B) then the obvious way is to have B read a table or
watch for some other event and then act accordingly. What exactly do you
want to achieve? Is this simply about optimistic locking?

--
David Portas
SQL Server MVP
--|||dubian (collatz@.bigtexansoftware.com) writes:
> I would like to propagate an event signal to an external application
> when a table in my MSSql2000 server is updated.
> Prog A; I have an external application adding records to a table.
> Prog B; I have another external application using this table as well.
> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event without polling the table or creating a link
> between Prog A and Prog B.

By far, the easiest way is to poll.

The other way would be to have a trigger on the table, that fires of an
extended stored procedure or OLE object to somehow send a singal to
Process B. Since extended stored procedures and local OLE objects in
the same memory space as the rest of SQL Server, they are somewhat
dangerous: if they crash on an access violation, the entire server
goes belly-up.

I believe that you also can use the sp_OAxxx routiens to start an
OLE object on a remote server. In this there is less risk for crashes.
But this is like to take time, and when you are in a trigger you are
in a transaction hold locks. If the update frequency is high, this
trigger can kill your throughput.

A variation is to have the trigger to write to a table, and then
run a job from SQL Server Agent that reads the table and alerts the
other process. Such a job would run once a minute or so. Since this job
could be an ActiveX task you could signal with XP:s and that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Propagate NEW Table to Anon Merge Replicas

How do I get a New Table to propagate to Anonymous Merge Subscribers, WITH
Add, Update, Delete Triggers?
I created a new Table (by running a SQL Script created on a Developer
machine) on a Remote SBS 2003 Server (SQL 2K). It Propagated to Subscribers,
OK. But it is useless, because is missing the Triggers: dbo.ins_ ... ,
dbo.upd_ ... , dbo.del_ ...
What did I miss, and Where? when I created the Prototype?
Aubrey Kelley
It sounds like merge replication has not placed all of the object it
requires on the subscriber. Are all objects owned by dbo? There were some
problems on pre SP 4 service packs with this.
How did you create the new table and add it to the merge publication? How
did you deploy it to your subscribers?
The best way to fix this is to reinitialize, generate a new snapshot and
send it to the subscribers. Any other approach may fix this particular
problem but there could be further missing objects and the merge replication
metadata may be in an inconsistent state.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:B1E8B402-7B5B-4DD3-8389-9BBC7D99F109@.microsoft.com...
> How do I get a New Table to propagate to Anonymous Merge Subscribers, WITH
> Add, Update, Delete Triggers?
> I created a new Table (by running a SQL Script created on a Developer
> machine) on a Remote SBS 2003 Server (SQL 2K). It Propagated to
> Subscribers,
> OK. But it is useless, because is missing the Triggers: dbo.ins_ ... ,
> dbo.upd_ ... , dbo.del_ ...
> What did I miss, and Where? when I created the Prototype?
> --
> Aubrey Kelley

Propagate fields from elements 2 or 3 levels above current element

Hi,
I am currently trying to insert multilevel data to 5 tables from one xml
source file. It inserts without errors, however, I noticed that I can only
propagate element values to the child only if the element value is from a
hierarchy above it.
For example, I have the following hierarchies/entities within a file:
<CONTROL>
<ORDER> (repeating)
<ORDER_ITEM> (repeating)
<FEATURES> (repeating)
</FEATURES>
</ORDER_ITEM>
<WORK_INSTRUCTIONS> (repeating)
</WORK_INSTRUCTIONS>
</ORDER>
</CONTROL>
In the table destined to hold fields for <FEATURES>, I also included one
field from <CONTROL> and another from <ORDER>. However, both remain blank
when I look at this table. Instead, only the the line number field from
<ORDER_ITEM> show up in addition to the fields from <FEATURES>. Is there
another way I could structure my annotated schema or tables to be able to
hold these needed fields?
I can post or send my table DDL, my annotated schema, and the sample xml
file upon request.
Regards,
Tristan
I had forgotten to bring up earlier that I am using SQLXMLBulkLoad object
from SQLXML 3 sp3 to upload the xml document to the 5 tables described.
5 tables described are as follows:
[DF_TempOrder_Control] ([XctlMsgNum],[Transaction],[Schema], [Plant],
[ParNum], [Date],[Time], [Valid])
[DF_TempOrder_Header] ([XctlMsgNum],[OrdMsgNum],[Action],[Floor_Order],
[Order_Type],[Due_Date],[Cra_Date],[AddrNumber],[Country],
[Zipcode], [Master_Order], [Ship_Tie] [nvarchar],
[RPQ], [Valid])
[DF_TempOrder_Details] ([XctlMsgNum], [Floor_Order], [MATNR],
[MATNR_DESC], [KDMAT], [KDMAT_DESC], [REVISION],
[SERIAL_YN], [REVISION_YN], [UPC_PART_YN],
[CONSIGNED_YN],[MAC_YN], [QTY],
[FLOOR_POSNR], [PARENT_POSNR], [BUNDLE_POSNR],
[VBAP_POSNR], [LIPS_POSNR], [VBAP_POSEX],
[SALES_ORDER],[SOLD_CUST_PO],[DELIVERY_NUM],
[SHIP_CUST_PO],[MACHINE_MODEL], [Valid])
[DF_TempOrder_ItemFeatures] ([XctlMsgNum],
[Floor_Order], [FLOOR_POSNR],
[Feature], [Valid])
[DF_TempOrder_Work_Instructions] ([XctlMsgNum],
[Floor_Order], [Instruction], [Valid])
My annotated schema is as follows:
<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="Order_Control" parent="DF_TempOrder_Control"
parent-key="XCTLMSGNUM" child="DF_TempOrder_Header"
child-key="XCTLMSGNUM" />
<sql:relationship name="Order_Detail" parent="DF_TempOrder_Header"
parent-key="FLOOR_ORDER" child="DF_TempOrder_Details"
child-key="FLOOR_ORDER" />
<sql:relationship name="OrderWI" parent="DF_TempOrder_Header"
parent-key="FLOOR_ORDER" child="DF_TempOrder_Work_Instructions"
child-key="FLOOR_ORDER" />
<sql:relationship name="OrderItemFeature" parent="DF_TempOrder_Details"
parent-key="FLOOR_POSNR" child="DF_TempOrder_Features"
child-key="FLOOR_POSNR" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="CONTROL" sql:relation="DF_TempOrder_Control">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="XCTLMSGNUM" type="xsd:string" />
<xsd:element name="TRANSACTION" type="xsd:string" />
<xsd:element name="SCHEMA" type="xsd:string" />
<xsd:element name="PLANT" type="xsd:string" />
<xsd:element name="PARNUM" type="xsd:string" />
<xsd:element name="DATE" type="xsd:string" />
<xsd:element name="TIME" type="xsd:string" />
<xsd:element name="ORDER" sql:relation="DF_TempOrder_Header"
sql:relationship="Order_Control">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FLOOR_ORDER" type="xsd:string" />
<xsd:element name="ORDMSGNUM" type="xsd:string" />
<xsd:element name="ACTION" type="xsd:string" />
<xsd:element name="ORDER_TYPE" type="xsd:string" />
<xsd:element name="DUE_DATE" type="xsd:string" />
<xsd:element name="CRA_DATE" type="xsd:string" />
<xsd:element name="ADDRNUMBER" type="xsd:integer" />
<xsd:element name="COUNTRY" type="xsd:string" />
<xsd:element name="ZIPCODE" type="xsd:string" />
<xsd:element name="MASTER_ORDER" type="xsd:string" />
<xsd:element name="SHIP_TIE" type="xsd:string" />
<xsd:element name="RPQ" type="xsd:string" />
<xsd:element name="ORDER_ITEM" sql:relation="DF_TempOrder_Details"
sql:relationship="Order_Detail">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MATNR" type="xsd:string" />
<xsd:element name="MATNR_DESC" type="xsd:string" />
<xsd:element name="KDMAT" type="xsd:string" />
<xsd:element name="KDMAT_DESC" type="xsd:string" />
<xsd:element name="REVISION" type="xsd:string" />
<xsd:element name="SERIAL_YN" type="xsd:string" />
<xsd:element name="REVISION_YN" type="xsd:string" />
<xsd:element name="UPC_PART_YN" type="xsd:string" />
<xsd:element name="CONSIGNED_YN" type="xsd:string" />
<xsd:element name="MAC_YN" type="xsd:string" />
<xsd:element name="QTY" type="xsd:int" />
<xsd:element name="FLOOR_POSNR" type="xsd:string" />
<xsd:element name="PARENT_POSNR" type="xsd:string" />
<xsd:element name="BUNDLE_POSNR" type="xsd:string" />
<xsd:element name="VBAP_POSNR" type="xsd:string" />
<xsd:element name="LIPS_POSNR" type="xsd:string" />
<xsd:element name="VBAP_POSEX" type="xsd:string" />
<xsd:element name="SALES_ORDER" type="xsd:string" />
<xsd:element name="SOLD_CUST_PO" type="xsd:string" />
<xsd:element name="DELIVERY_NUM" type="xsd:string" />
<xsd:element name="SHIP_CUST_PO" type="xsd:string" />
<xsd:element name="MACHINE_MODEL" type="xsd:string" />
<xsd:element name="FEATURES" sql:relation="DF_TempOrder_Features"
sql:relationship="OrderItemFeature">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FEATURE" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="WORK_INSTRUCTIONS"
sql:relation="DF_TempOrder_Work_Instructions" sql:relationship="OrderWI">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INSTRUCTION" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My sample xml file is as follows:
<MESSAGE>
<CONTROL>
<XCTLMSGNUM>123456789</XCTLMSGNUM>
<TRANSACTION>ORDER FOR FLOOR</TRANSACTION>
<SCHEMA>ORDER CONTENT</SCHEMA>
<PLANT>TN01</PLANT>
<PARNUM>0011</PARNUM>
<DATE>20050427</DATE>
<TIME>1237</TIME>
<ORDER>
<ORDMSGNUM>123456</ORDMSGNUM>
<ACTION>ADD</ACTION>
<FLOOR_ORDER>SAP123456</FLOOR_ORDER>
<ORDER_TYPE>ZDBO</ORDER_TYPE>
<ADDRNUMBER>123456</ADDRNUMBER>
<COUNTRY>US</COUNTRY>
<ZIPCODE>12345</ZIPCODE>
<MASTER_ORDER>DOC12345</MASTER_ORDER>
<SHIP_TIE>10823741</SHIP_TIE>
<DUE_DATE>05302005</DUE_DATE>
<CRA_DATE>06302005</CRA_DATE>
<RPQ>12345</RPQ>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION</INSTRUCTION>
</WORK_INSTRUCTIONS>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION1</INSTRUCTION>
</WORK_INSTRUCTIONS>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION2</INSTRUCTION>
</WORK_INSTRUCTIONS>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
</ORDER>
</CONTROL>
</MESSAGE>
|||Unfortunately, there is no possible mapping to perform the below action for
the given input Xml.
However, you may apply XSLT or wrap the SAX reader to inject fields which
you would like to appear in different levels. This is the one possible
workaround I can think.
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:ED8CDC51-FB41-4867-B77E-E92855DF11FB@.microsoft.com...
>I had forgotten to bring up earlier that I am using SQLXMLBulkLoad object
> from SQLXML 3 sp3 to upload the xml document to the 5 tables described.
> 5 tables described are as follows:
> [DF_TempOrder_Control] ([XctlMsgNum],[Transaction],[Schema], [Plant],
> [ParNum], [Date],[Time], [Valid])
> [DF_TempOrder_Header] ([XctlMsgNum],[OrdMsgNum],[Action],[Floor_Order],
> [Order_Type],[Due_Date],[Cra_Date],[AddrNumber],[Country],
> [Zipcode], [Master_Order], [Ship_Tie] [nvarchar],
> [RPQ], [Valid])
> [DF_TempOrder_Details] ([XctlMsgNum], [Floor_Order], [MATNR],
> [MATNR_DESC], [KDMAT], [KDMAT_DESC], [REVISION],
> [SERIAL_YN], [REVISION_YN], [UPC_PART_YN],
> [CONSIGNED_YN], [MAC_YN], [QTY],
> [FLOOR_POSNR], [PARENT_POSNR], [BUNDLE_POSNR],
> [VBAP_POSNR], [LIPS_POSNR], [VBAP_POSEX],
> [SALES_ORDER], [SOLD_CUST_PO], [DELIVERY_NUM],
> [SHIP_CUST_PO], [MACHINE_MODEL], [Valid])
> [DF_TempOrder_ItemFeatures] ([XctlMsgNum],
> [Floor_Order], [FLOOR_POSNR],
> [Feature], [Valid])
> [DF_TempOrder_Work_Instructions] ([XctlMsgNum],
> [Floor_Order], [Instruction], [Valid])
> My annotated schema is as follows:
> <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="Order_Control" parent="DF_TempOrder_Control"
> parent-key="XCTLMSGNUM" child="DF_TempOrder_Header"
> child-key="XCTLMSGNUM" />
> <sql:relationship name="Order_Detail" parent="DF_TempOrder_Header"
> parent-key="FLOOR_ORDER" child="DF_TempOrder_Details"
> child-key="FLOOR_ORDER" />
> <sql:relationship name="OrderWI" parent="DF_TempOrder_Header"
> parent-key="FLOOR_ORDER" child="DF_TempOrder_Work_Instructions"
> child-key="FLOOR_ORDER" />
> <sql:relationship name="OrderItemFeature" parent="DF_TempOrder_Details"
> parent-key="FLOOR_POSNR" child="DF_TempOrder_Features"
> child-key="FLOOR_POSNR" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="CONTROL" sql:relation="DF_TempOrder_Control">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="XCTLMSGNUM" type="xsd:string" />
> <xsd:element name="TRANSACTION" type="xsd:string" />
> <xsd:element name="SCHEMA" type="xsd:string" />
> <xsd:element name="PLANT" type="xsd:string" />
> <xsd:element name="PARNUM" type="xsd:string" />
> <xsd:element name="DATE" type="xsd:string" />
> <xsd:element name="TIME" type="xsd:string" />
> <xsd:element name="ORDER" sql:relation="DF_TempOrder_Header"
> sql:relationship="Order_Control">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="FLOOR_ORDER" type="xsd:string" />
> <xsd:element name="ORDMSGNUM" type="xsd:string" />
> <xsd:element name="ACTION" type="xsd:string" />
> <xsd:element name="ORDER_TYPE" type="xsd:string" />
> <xsd:element name="DUE_DATE" type="xsd:string" />
> <xsd:element name="CRA_DATE" type="xsd:string" />
> <xsd:element name="ADDRNUMBER" type="xsd:integer" />
> <xsd:element name="COUNTRY" type="xsd:string" />
> <xsd:element name="ZIPCODE" type="xsd:string" />
> <xsd:element name="MASTER_ORDER" type="xsd:string" />
> <xsd:element name="SHIP_TIE" type="xsd:string" />
> <xsd:element name="RPQ" type="xsd:string" />
> <xsd:element name="ORDER_ITEM" sql:relation="DF_TempOrder_Details"
> sql:relationship="Order_Detail">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MATNR" type="xsd:string" />
> <xsd:element name="MATNR_DESC" type="xsd:string" />
> <xsd:element name="KDMAT" type="xsd:string" />
> <xsd:element name="KDMAT_DESC" type="xsd:string" />
> <xsd:element name="REVISION" type="xsd:string" />
> <xsd:element name="SERIAL_YN" type="xsd:string" />
> <xsd:element name="REVISION_YN" type="xsd:string" />
> <xsd:element name="UPC_PART_YN" type="xsd:string" />
> <xsd:element name="CONSIGNED_YN" type="xsd:string" />
> <xsd:element name="MAC_YN" type="xsd:string" />
> <xsd:element name="QTY" type="xsd:int" />
> <xsd:element name="FLOOR_POSNR" type="xsd:string" />
> <xsd:element name="PARENT_POSNR" type="xsd:string" />
> <xsd:element name="BUNDLE_POSNR" type="xsd:string" />
> <xsd:element name="VBAP_POSNR" type="xsd:string" />
> <xsd:element name="LIPS_POSNR" type="xsd:string" />
> <xsd:element name="VBAP_POSEX" type="xsd:string" />
> <xsd:element name="SALES_ORDER" type="xsd:string" />
> <xsd:element name="SOLD_CUST_PO" type="xsd:string" />
> <xsd:element name="DELIVERY_NUM" type="xsd:string" />
> <xsd:element name="SHIP_CUST_PO" type="xsd:string" />
> <xsd:element name="MACHINE_MODEL" type="xsd:string" />
> <xsd:element name="FEATURES" sql:relation="DF_TempOrder_Features"
> sql:relationship="OrderItemFeature">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="FEATURE" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="WORK_INSTRUCTIONS"
> sql:relation="DF_TempOrder_Work_Instructions" sql:relationship="OrderWI">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INSTRUCTION" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> My sample xml file is as follows:
> <MESSAGE>
> <CONTROL>
> <XCTLMSGNUM>123456789</XCTLMSGNUM>
> <TRANSACTION>ORDER FOR FLOOR</TRANSACTION>
> <SCHEMA>ORDER CONTENT</SCHEMA>
> <PLANT>TN01</PLANT>
> <PARNUM>0011</PARNUM>
> <DATE>20050427</DATE>
> <TIME>1237</TIME>
> <ORDER>
> <ORDMSGNUM>123456</ORDMSGNUM>
> <ACTION>ADD</ACTION>
> <FLOOR_ORDER>SAP123456</FLOOR_ORDER>
> <ORDER_TYPE>ZDBO</ORDER_TYPE>
> <ADDRNUMBER>123456</ADDRNUMBER>
> <COUNTRY>US</COUNTRY>
> <ZIPCODE>12345</ZIPCODE>
> <MASTER_ORDER>DOC12345</MASTER_ORDER>
> <SHIP_TIE>10823741</SHIP_TIE>
> <DUE_DATE>05302005</DUE_DATE>
> <CRA_DATE>06302005</CRA_DATE>
> <RPQ>12345</RPQ>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION1</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION2</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> </ORDER>
> </CONTROL>
> </MESSAGE>
|||Other solutions include:
Write your own shredder on the midtier.
Pass the data to the database and use OpenXML in SS2000 (or nodes() in
SS2005) to get at the parent.
Best regards
Michael
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:uUX%23VvRbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Unfortunately, there is no possible mapping to perform the below action
> for the given input Xml.
> However, you may apply XSLT or wrap the SAX reader to inject fields which
> you would like to appear in different levels. This is the one possible
> workaround I can think.
>
> "Tristan" <Tristan@.discussions.microsoft.com> wrote in message
> news:ED8CDC51-FB41-4867-B77E-E92855DF11FB@.microsoft.com...
>

Propagate fields from elements 2 or 3 levels above current element

Hi,
I am currently trying to insert multilevel data to 5 tables from one xml
source file. It inserts without errors, however, I noticed that I can only
propagate element values to the child only if the element value is from a
hierarchy above it.
For example, I have the following hierarchies/entities within a file:
<CONTROL>
<ORDER> (repeating)
<ORDER_ITEM> (repeating)
<FEATURES> (repeating)
</FEATURES>
</ORDER_ITEM>
<WORK_INSTRUCTIONS> (repeating)
</WORK_INSTRUCTIONS>
</ORDER>
</CONTROL>
In the table destined to hold fields for <FEATURES>, I also included one
field from <CONTROL> and another from <ORDER>. However, both remain blank
when I look at this table. Instead, only the the line number field from
<ORDER_ITEM> show up in addition to the fields from <FEATURES>. Is there
another way I could structure my annotated schema or tables to be able to
hold these needed fields?
I can post or send my table DDL, my annotated schema, and the sample xml
file upon request.
Regards,
TristanI had forgotten to bring up earlier that I am using SQLXMLBulkLoad object
from SQLXML 3 sp3 to upload the xml document to the 5 tables described.
5 tables described are as follows:
[DF_TempOrder_Control] ([XctlMsgNum],[Transaction],[Schema], [Plant],
[ParNum], [Date],[Time], [Valid])
[DF_TempOrder_Header] ([XctlMsgNum],[OrdMsgNum],[Action],[Floo
r_Order],
[Order_Type],[Due_Date],[Cra_Date],[Addr
Number],[Country],
[Zipcode], [Master_Order], [Ship_Tie] [nvarchar],
[RPQ], [Valid])
[DF_TempOrder_Details] ([XctlMsgNum], [Floor_Order], [MATNR],
[MATNR_DESC], [KDMAT], [KDMAT_DESC], [REVISION],
[SERIAL_YN], [REVISION_YN], [UPC_PART_YN],
[CONSIGNED_YN], [MAC_YN], [QTY],
[FLOOR_POSNR], [PARENT_POSNR], [BUNDLE_POSNR],
[VBAP_POSNR], [LIPS_POSNR], [VBAP_POSEX],
[SALES_ORDER], [SOLD_CUST_PO], [DELIVERY
_NUM],
[SHIP_CUST_PO], [MACHINE_MODEL], [Valid])
[DF_TempOrder_ItemFeatures] ([XctlMsgNum],
[Floor_Order], [FLOOR_POSNR],
[Feature], [Valid])
[DF_TempOrder_Work_Instructions] ([XctlMsgNum],
[Floor_Order], [Instruction], [Valid])
My annotated schema is as follows:
<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="Order_Control" parent="DF_TempOrder_Control"
parent-key="XCTLMSGNUM" child="DF_TempOrder_Header"
child-key="XCTLMSGNUM" />
<sql:relationship name="Order_Detail" parent="DF_TempOrder_Header"
parent-key="FLOOR_ORDER" child="DF_TempOrder_Details"
child-key="FLOOR_ORDER" />
<sql:relationship name="OrderWI" parent="DF_TempOrder_Header"
parent-key="FLOOR_ORDER" child="DF_TempOrder_Work_Instructions"
child-key="FLOOR_ORDER" />
<sql:relationship name="OrderItemFeature" parent="DF_TempOrder_Details"
parent-key="FLOOR_POSNR" child="DF_TempOrder_Features"
child-key="FLOOR_POSNR" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="CONTROL" sql:relation="DF_TempOrder_Control">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="XCTLMSGNUM" type="xsd:string" />
<xsd:element name="TRANSACTION" type="xsd:string" />
<xsd:element name="SCHEMA" type="xsd:string" />
<xsd:element name="PLANT" type="xsd:string" />
<xsd:element name="PARNUM" type="xsd:string" />
<xsd:element name="DATE" type="xsd:string" />
<xsd:element name="TIME" type="xsd:string" />
<xsd:element name="ORDER" sql:relation="DF_TempOrder_Header"
sql:relationship="Order_Control">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FLOOR_ORDER" type="xsd:string" />
<xsd:element name="ORDMSGNUM" type="xsd:string" />
<xsd:element name="ACTION" type="xsd:string" />
<xsd:element name="ORDER_TYPE" type="xsd:string" />
<xsd:element name="DUE_DATE" type="xsd:string" />
<xsd:element name="CRA_DATE" type="xsd:string" />
<xsd:element name="ADDRNUMBER" type="xsd:integer" />
<xsd:element name="COUNTRY" type="xsd:string" />
<xsd:element name="ZIPCODE" type="xsd:string" />
<xsd:element name="MASTER_ORDER" type="xsd:string" />
<xsd:element name="SHIP_TIE" type="xsd:string" />
<xsd:element name="RPQ" type="xsd:string" />
<xsd:element name="ORDER_ITEM" sql:relation="DF_TempOrder_Details"
sql:relationship="Order_Detail">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MATNR" type="xsd:string" />
<xsd:element name="MATNR_DESC" type="xsd:string" />
<xsd:element name="KDMAT" type="xsd:string" />
<xsd:element name="KDMAT_DESC" type="xsd:string" />
<xsd:element name="REVISION" type="xsd:string" />
<xsd:element name="SERIAL_YN" type="xsd:string" />
<xsd:element name="REVISION_YN" type="xsd:string" />
<xsd:element name="UPC_PART_YN" type="xsd:string" />
<xsd:element name="CONSIGNED_YN" type="xsd:string" />
<xsd:element name="MAC_YN" type="xsd:string" />
<xsd:element name="QTY" type="xsd:int" />
<xsd:element name="FLOOR_POSNR" type="xsd:string" />
<xsd:element name="PARENT_POSNR" type="xsd:string" />
<xsd:element name="BUNDLE_POSNR" type="xsd:string" />
<xsd:element name="VBAP_POSNR" type="xsd:string" />
<xsd:element name="LIPS_POSNR" type="xsd:string" />
<xsd:element name="VBAP_POSEX" type="xsd:string" />
<xsd:element name="SALES_ORDER" type="xsd:string" />
<xsd:element name="SOLD_CUST_PO" type="xsd:string" />
<xsd:element name="DELIVERY_NUM" type="xsd:string" />
<xsd:element name="SHIP_CUST_PO" type="xsd:string" />
<xsd:element name="MACHINE_MODEL" type="xsd:string" />
<xsd:element name="FEATURES" sql:relation="DF_TempOrder_Features"
sql:relationship="OrderItemFeature">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FEATURE" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="WORK_INSTRUCTIONS"
sql:relation="DF_TempOrder_Work_Instructions" sql:relationship="OrderWI">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INSTRUCTION" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My sample xml file is as follows:
<MESSAGE>
<CONTROL>
<XCTLMSGNUM>123456789</XCTLMSGNUM>
<TRANSACTION>ORDER FOR FLOOR</TRANSACTION>
<SCHEMA>ORDER CONTENT</SCHEMA>
<PLANT>TN01</PLANT>
<PARNUM>0011</PARNUM>
<DATE>20050427</DATE>
<TIME>1237</TIME>
<ORDER>
<ORDMSGNUM>123456</ORDMSGNUM>
<ACTION>ADD</ACTION>
<FLOOR_ORDER>SAP123456</FLOOR_ORDER>
<ORDER_TYPE>ZDBO</ORDER_TYPE>
<ADDRNUMBER>123456</ADDRNUMBER>
<COUNTRY>US</COUNTRY>
<ZIPCODE>12345</ZIPCODE>
<MASTER_ORDER>DOC12345</MASTER_ORDER>
<SHIP_TIE>10823741</SHIP_TIE>
<DUE_DATE>05302005</DUE_DATE>
<CRA_DATE>06302005</CRA_DATE>
<RPQ>12345</RPQ>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION</INSTRUCTION>
</WORK_INSTRUCTIONS>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION1</INSTRUCTION>
</WORK_INSTRUCTIONS>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION2</INSTRUCTION>
</WORK_INSTRUCTIONS>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
</ORDER>
</CONTROL>
</MESSAGE>|||Unfortunately, there is no possible mapping to perform the below action for
the given input Xml.
However, you may apply XSLT or wrap the SAX reader to inject fields which
you would like to appear in different levels. This is the one possible
workaround I can think.
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:ED8CDC51-FB41-4867-B77E-E92855DF11FB@.microsoft.com...
>I had forgotten to bring up earlier that I am using SQLXMLBulkLoad object
> from SQLXML 3 sp3 to upload the xml document to the 5 tables described.
> 5 tables described are as follows:
> [DF_TempOrder_Control] ([XctlMsgNum],[Transaction],[Schema], [Plant],
> [ParNum], [Date],[Time], [Valid])
> [DF_TempOrder_Header] ([XctlMsgNum],[OrdMsgNum],[Action],[Floo
r_Order],
> [Order_Type],[Due_Date],[Cra_Date],[Addr
Number],[Country],
> [Zipcode], [Master_Order], [Ship_Tie] [nvarchar],
> [RPQ], [Valid])
> [DF_TempOrder_Details] ([XctlMsgNum], [Floor_Order], [MATNR],
> [MATNR_DESC], [KDMAT], [KDMAT_DESC], [REVISION],
> [SERIAL_YN], [REVISION_YN], [UPC_PART_YN],
> [CONSIGNED_YN], [MAC_YN], [QTY],
> [FLOOR_POSNR], [PARENT_POSNR], [BUNDLE_POSNR],
> [VBAP_POSNR], [LIPS_POSNR], [VBAP_POSEX],
> [SALES_ORDER], [SOLD_CUST_PO], [DELIVERY_NUM],
> [SHIP_CUST_PO], [MACHINE_MODEL], [Valid])
> [DF_TempOrder_ItemFeatures] ([XctlMsgNum],
> [Floor_Order], [FLOOR_POSNR],
> [Feature], [Valid])
> [DF_TempOrder_Work_Instructions] ([XctlMsgNum],
> [Floor_Order], [Instruction], [Valid])
> My annotated schema is as follows:
> <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="Order_Control" parent="DF_TempOrder_Control"
> parent-key="XCTLMSGNUM" child="DF_TempOrder_Header"
> child-key="XCTLMSGNUM" />
> <sql:relationship name="Order_Detail" parent="DF_TempOrder_Header"
> parent-key="FLOOR_ORDER" child="DF_TempOrder_Details"
> child-key="FLOOR_ORDER" />
> <sql:relationship name="OrderWI" parent="DF_TempOrder_Header"
> parent-key="FLOOR_ORDER" child="DF_TempOrder_Work_Instructions"
> child-key="FLOOR_ORDER" />
> <sql:relationship name="OrderItemFeature" parent="DF_TempOrder_Details"
> parent-key="FLOOR_POSNR" child="DF_TempOrder_Features"
> child-key="FLOOR_POSNR" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="CONTROL" sql:relation="DF_TempOrder_Control">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="XCTLMSGNUM" type="xsd:string" />
> <xsd:element name="TRANSACTION" type="xsd:string" />
> <xsd:element name="SCHEMA" type="xsd:string" />
> <xsd:element name="PLANT" type="xsd:string" />
> <xsd:element name="PARNUM" type="xsd:string" />
> <xsd:element name="DATE" type="xsd:string" />
> <xsd:element name="TIME" type="xsd:string" />
> <xsd:element name="ORDER" sql:relation="DF_TempOrder_Header"
> sql:relationship="Order_Control">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="FLOOR_ORDER" type="xsd:string" />
> <xsd:element name="ORDMSGNUM" type="xsd:string" />
> <xsd:element name="ACTION" type="xsd:string" />
> <xsd:element name="ORDER_TYPE" type="xsd:string" />
> <xsd:element name="DUE_DATE" type="xsd:string" />
> <xsd:element name="CRA_DATE" type="xsd:string" />
> <xsd:element name="ADDRNUMBER" type="xsd:integer" />
> <xsd:element name="COUNTRY" type="xsd:string" />
> <xsd:element name="ZIPCODE" type="xsd:string" />
> <xsd:element name="MASTER_ORDER" type="xsd:string" />
> <xsd:element name="SHIP_TIE" type="xsd:string" />
> <xsd:element name="RPQ" type="xsd:string" />
> <xsd:element name="ORDER_ITEM" sql:relation="DF_TempOrder_Details"
> sql:relationship="Order_Detail">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MATNR" type="xsd:string" />
> <xsd:element name="MATNR_DESC" type="xsd:string" />
> <xsd:element name="KDMAT" type="xsd:string" />
> <xsd:element name="KDMAT_DESC" type="xsd:string" />
> <xsd:element name="REVISION" type="xsd:string" />
> <xsd:element name="SERIAL_YN" type="xsd:string" />
> <xsd:element name="REVISION_YN" type="xsd:string" />
> <xsd:element name="UPC_PART_YN" type="xsd:string" />
> <xsd:element name="CONSIGNED_YN" type="xsd:string" />
> <xsd:element name="MAC_YN" type="xsd:string" />
> <xsd:element name="QTY" type="xsd:int" />
> <xsd:element name="FLOOR_POSNR" type="xsd:string" />
> <xsd:element name="PARENT_POSNR" type="xsd:string" />
> <xsd:element name="BUNDLE_POSNR" type="xsd:string" />
> <xsd:element name="VBAP_POSNR" type="xsd:string" />
> <xsd:element name="LIPS_POSNR" type="xsd:string" />
> <xsd:element name="VBAP_POSEX" type="xsd:string" />
> <xsd:element name="SALES_ORDER" type="xsd:string" />
> <xsd:element name="SOLD_CUST_PO" type="xsd:string" />
> <xsd:element name="DELIVERY_NUM" type="xsd:string" />
> <xsd:element name="SHIP_CUST_PO" type="xsd:string" />
> <xsd:element name="MACHINE_MODEL" type="xsd:string" />
> <xsd:element name="FEATURES" sql:relation="DF_TempOrder_Features"
> sql:relationship="OrderItemFeature">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="FEATURE" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="WORK_INSTRUCTIONS"
> sql:relation="DF_TempOrder_Work_Instructions" sql:relationship="OrderWI">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INSTRUCTION" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> My sample xml file is as follows:
> <MESSAGE>
> <CONTROL>
> <XCTLMSGNUM>123456789</XCTLMSGNUM>
> <TRANSACTION>ORDER FOR FLOOR</TRANSACTION>
> <SCHEMA>ORDER CONTENT</SCHEMA>
> <PLANT>TN01</PLANT>
> <PARNUM>0011</PARNUM>
> <DATE>20050427</DATE>
> <TIME>1237</TIME>
> <ORDER>
> <ORDMSGNUM>123456</ORDMSGNUM>
> <ACTION>ADD</ACTION>
> <FLOOR_ORDER>SAP123456</FLOOR_ORDER>
> <ORDER_TYPE>ZDBO</ORDER_TYPE>
> <ADDRNUMBER>123456</ADDRNUMBER>
> <COUNTRY>US</COUNTRY>
> <ZIPCODE>12345</ZIPCODE>
> <MASTER_ORDER>DOC12345</MASTER_ORDER>
> <SHIP_TIE>10823741</SHIP_TIE>
> <DUE_DATE>05302005</DUE_DATE>
> <CRA_DATE>06302005</CRA_DATE>
> <RPQ>12345</RPQ>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION1</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION2</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> </ORDER>
> </CONTROL>
> </MESSAGE>sql