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...
>
Showing posts with label inserts. Show all posts
Showing posts with label inserts. Show all posts
Friday, March 23, 2012
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
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
Saturday, February 25, 2012
Programmatic inspection of a dump?
I need to set up a job to allow users to restore their databases, on SQL Server 2000 SP3. The idea is that a user inserts a record into a table, identifying the dump they want to load. (They can only restore their own account.) A job picks up this record, restores the database, and notifies the user as appropriate.
My part of this is writing the procedure that the job executes, including the dump restore. Part of that is getting each dump's file groups (data, index, and log) into the proper locations for this server and this user.
Essentially, I need to be able to access the results of 'load filelistonly' from a cursor. How do I access the file list?Essentially, I need to be able to access the results of 'load filelistonly' from a cursor. How do I access the file list?
Google is your friend. http://www.karaszi.com/sqlserver/util_restore_all_in_file.asp|||Man .. its better if you do not call the backup a dump (You know what I mean !!!)... coz its what will save your A$$ when the database goes down ...
My part of this is writing the procedure that the job executes, including the dump restore. Part of that is getting each dump's file groups (data, index, and log) into the proper locations for this server and this user.
Essentially, I need to be able to access the results of 'load filelistonly' from a cursor. How do I access the file list?Essentially, I need to be able to access the results of 'load filelistonly' from a cursor. How do I access the file list?
Google is your friend. http://www.karaszi.com/sqlserver/util_restore_all_in_file.asp|||Man .. its better if you do not call the backup a dump (You know what I mean !!!)... coz its what will save your A$$ when the database goes down ...
Subscribe to:
Posts (Atom)