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 errors. Show all posts
Showing posts with label errors. 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
Monday, March 12, 2012
Programmaticly check DB
Hi,
I want to write a job that will check for DB errors (as in dbcc checkdb) and
will operate if errors exists.
Is there a way to extract errors returned by dbcc checkdb?Have you consider setting up a DB Maintenance plan..? If you use the
Database Maintenance Plan Wizard you get the option to fix any errors the
checking finds. This can be scheduled to run as a job
HTH
Ryan Waight, MCDBA, MCSE
"Hagay Lupesko" <hagayl@.nice.com> wrote in message
news:ukCzmFZkDHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to write a job that will check for DB errors (as in dbcc checkdb)
and
> will operate if errors exists.
> Is there a way to extract errors returned by dbcc checkdb?
>|||That maintenance plan option does require the database to be in single user
mode though, even when it doesn't have to repair anything. That can cause
quite some problems. I prefer to have dbcc checkdb run as a job and check if
the job runs as expected and act when something goes wrong. At least then I
know if something goes wrong, which might indicate an underlying problem,
rather than have problems repaired automatically.
--
Jacco Schalkwijk
SQL Server MVP
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uZqZKYZkDHA.2232@.TK2MSFTNGP09.phx.gbl...
> Have you consider setting up a DB Maintenance plan..? If you use the
> Database Maintenance Plan Wizard you get the option to fix any errors the
> checking finds. This can be scheduled to run as a job
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> news:ukCzmFZkDHA.688@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I want to write a job that will check for DB errors (as in dbcc checkdb)
> and
> > will operate if errors exists.
> >
> > Is there a way to extract errors returned by dbcc checkdb?
> >
> >
>
I want to write a job that will check for DB errors (as in dbcc checkdb) and
will operate if errors exists.
Is there a way to extract errors returned by dbcc checkdb?Have you consider setting up a DB Maintenance plan..? If you use the
Database Maintenance Plan Wizard you get the option to fix any errors the
checking finds. This can be scheduled to run as a job
HTH
Ryan Waight, MCDBA, MCSE
"Hagay Lupesko" <hagayl@.nice.com> wrote in message
news:ukCzmFZkDHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to write a job that will check for DB errors (as in dbcc checkdb)
and
> will operate if errors exists.
> Is there a way to extract errors returned by dbcc checkdb?
>|||That maintenance plan option does require the database to be in single user
mode though, even when it doesn't have to repair anything. That can cause
quite some problems. I prefer to have dbcc checkdb run as a job and check if
the job runs as expected and act when something goes wrong. At least then I
know if something goes wrong, which might indicate an underlying problem,
rather than have problems repaired automatically.
--
Jacco Schalkwijk
SQL Server MVP
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uZqZKYZkDHA.2232@.TK2MSFTNGP09.phx.gbl...
> Have you consider setting up a DB Maintenance plan..? If you use the
> Database Maintenance Plan Wizard you get the option to fix any errors the
> checking finds. This can be scheduled to run as a job
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> news:ukCzmFZkDHA.688@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I want to write a job that will check for DB errors (as in dbcc checkdb)
> and
> > will operate if errors exists.
> >
> > Is there a way to extract errors returned by dbcc checkdb?
> >
> >
>
Monday, February 20, 2012
Programatically created DataFLow task fails
Hi,
I have a package in which i have programatically created dataflow task. It used to work fine but now it fails with series of errors out of which one is below
Error 30002: Type 'MainPipe' is not defined. Line 63 Columns 33-40 Line Text: Dim DataFlowTask As MainPipe = CType(DataFlowTaskHost.InnerObject, MainPipe)
It seems all the function and classes referd from following dlls is not working:
Microsoft.SqlServer.DTSRuntimeWrap.dll
Microsoft.SqlServer.DTSPipelineWrap.dll
I say so because i get the following error also:
Error 30652: Reference required to assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' containing the type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90'. Add one to your project. Line 86 Columns 33-64 Line Text: DtsConvert.ToConnectionManager90(ChildPackage.Connections(""Source""))
These dlls are in GAC and in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies with same version but still it gives above error.
Thanks
Mohit
Mohit,
If it used to work and now it doesn't (as you suggest) then if it were me I would try and track down what got changed in the interim.
-Jamie
|||Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
|||
MohitGupta wrote:
Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
yes, its certainly a possibility. That's what I meant by identify what has changed. Code changes? Service pack changes? Windows Update changes? They're all changes that could *potentially* cause problems.
-Jamie
|||
MohitGupta wrote:
Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
yes, its certainly a possibility. That's what I meant by identify what has changed. Code changes? Service pack changes? Windows Update changes? They're all changes that could *potentially* cause problems.
-Jamie
Any pointers to the article resolving this specific error will be very helpful.
Mohit
|||
Jamie Thomson wrote:
MohitGupta wrote:
Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
yes, its certainly a possibility. That's what I meant by identify what has changed. Code changes? Service pack changes? Windows Update changes? They're all changes that could *potentially* cause problems.
-Jamie
Any pointers to the article resolving this specific error will be very helpful.
Mohit
Have you added the reference that it talks about?
-Jamie
|||
MohitGupta wrote:
Jamie Thomson wrote:
MohitGupta wrote:
Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
yes, its certainly a possibility. That's what I meant by identify what has changed. Code changes? Service pack changes? Windows Update changes? They're all changes that could *potentially* cause problems.
-Jamie
Any pointers to the article resolving this specific error will be very helpful.
Mohit
Have you added the reference that it talks about?
-Jamie
Yes the dlls are refered from the following location: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
|||
Yes the dlls are refered from the following location: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
That's wrong. Reference them from the GAC.
-Jamie
|||
MohitGupta wrote:
Yes the dlls are refered from the following location: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
That's wrong. Reference them from the GAC.
-Jamie
When the dll reference is added there is no option to browse and add assembly. It shows .NET assemblies which are from GAC out of which both the dll have this path: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies in the property box. This is on my development machine. But in dtsx file, it does not have any reference of this path. Below is the code generated used in production server:
<Reference
Name = "Microsoft.SqlServer.DTSPipelineWrap"
AssemblyName = "Microsoft.SqlServer.DTSPipelineWrap"
/>
<Reference
Name = "Microsoft.SQLServer.DTSRuntimeWrap"
AssemblyName = "Microsoft.SqlServer.DTSRuntimeWrap"
/>
I assume it is coming from GAC.
-Mohit
|||
Jamie Thomson wrote: When the dll reference is added there is no option to browse and add assembly. It shows .NET assemblies which are from GAC out of which both the dll have this path: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies in the property box. This is on my development machine. But in dtsx file, it does not have any reference of this path. Below is the code generated used in production server:
<Reference
Name = "Microsoft.SqlServer.DTSPipelineWrap"
AssemblyName = "Microsoft.SqlServer.DTSPipelineWrap"
/>
<Reference
Name = "Microsoft.SQLServer.DTSRuntimeWrap"
AssemblyName = "Microsoft.SqlServer.DTSRuntimeWrap"
/>
I assume it is coming from GAC.
-Mohit
This "property box" that you speak of. What object does that contain the properties of? On my machine I check properties of Microsoft.SqlServer.DTSPipelineWrap in c:\windows\assembly and there is no mention of a path anywhere. I'm confused.
The .dtsx file will not contain paths to DLLs, that's the whole point of GACing them.
-Jamie
|||Finally I got resolution to this issue
Cause:
Security Update for Microsoft .Net Framework 2.0 (KB928365) is installed on the server which causes SSIS packages to fail (Specifically custom data flow tasks using GACed dll)
Resolution:
Install this KB: SQLServer2005-KB932557-x86-ENU.exe on the server.
Link: http://support.microsoft.com/kb/932557/
This KB while installing gives option to Install the patch only for SQL Server Integration Services 2005 and SQL Server Tools and Workstation Components 2005. This KB is not required to be installed for SQL Server Instances. (Restart not required)
Known Issue:
After installing this KB, few warnings may be displayed which are known issues with SSIS.
Thanks
Mohit
Programatically created DataFLow task fails
Hi,
I have a package in which i have programatically created dataflow task. It used to work fine but now it fails with series of errors out of which one is below
Error 30002: Type 'MainPipe' is not defined. Line 63 Columns 33-40 Line Text: Dim DataFlowTask As MainPipe = CType(DataFlowTaskHost.InnerObject, MainPipe)
It seems all the function and classes referd from following dlls is not working:
Microsoft.SqlServer.DTSRuntimeWrap.dll
Microsoft.SqlServer.DTSPipelineWrap.dll
I say so because i get the following error also:
Error 30652: Reference required to assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' containing the type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90'. Add one to your project. Line 86 Columns 33-64 Line Text: DtsConvert.ToConnectionManager90(ChildPackage.Connections(""Source""))
These dlls are in GAC and in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies with same version but still it gives above error.
Thanks
Mohit
Mohit,
If it used to work and now it doesn't (as you suggest) then if it were me I would try and track down what got changed in the interim.
-Jamie
|||Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
|||
Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
yes, its certainly a possibility. That's what I meant by identify what has changed. Code changes? Service pack changes? Windows Update changes? They're all changes that could *potentially* cause problems.
-Jamie
|||
MohitGupta wrote:
Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
yes, its certainly a possibility. That's what I meant by identify what has changed. Code changes? Service pack changes? Windows Update changes? They're all changes that could *potentially* cause problems.
-Jamie
Any pointers to the article resolving this specific error will be very helpful.
Mohit
|||
Jamie Thomson wrote:
MohitGupta wrote:
Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
yes, its certainly a possibility. That's what I meant by identify what has changed. Code changes? Service pack changes? Windows Update changes? They're all changes that could *potentially* cause problems.
-Jamie
Any pointers to the article resolving this specific error will be very helpful.
Mohit
Have you added the reference that it talks about?
-Jamie
|||
MohitGupta wrote:
Jamie Thomson wrote:
MohitGupta wrote:
Hi Jamie,
There is no change in code because it was deployed in production. Is there possibility of this error being caused by any SQL server service pack or hotfix on the server.
Thanks
Mohit
yes, its certainly a possibility. That's what I meant by identify what has changed. Code changes? Service pack changes? Windows Update changes? They're all changes that could *potentially* cause problems.
-Jamie
Any pointers to the article resolving this specific error will be very helpful.
Mohit
Have you added the reference that it talks about?
-Jamie
Yes the dlls are refered from the following location: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
|||
Yes the dlls are refered from the following location: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
That's wrong. Reference them from the GAC.
-Jamie
|||
MohitGupta wrote:
Yes the dlls are refered from the following location: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
That's wrong. Reference them from the GAC.
-Jamie
When the dll reference is added there is no option to browse and add assembly. It shows .NET assemblies which are from GAC out of which both the dll have this path: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies in the property box. This is on my development machine. But in dtsx file, it does not have any reference of this path. Below is the code generated used in production server:
<Reference
Name = "Microsoft.SqlServer.DTSPipelineWrap"
AssemblyName = "Microsoft.SqlServer.DTSPipelineWrap"
/>
<Reference
Name = "Microsoft.SQLServer.DTSRuntimeWrap"
AssemblyName = "Microsoft.SqlServer.DTSRuntimeWrap"
/>
I assume it is coming from GAC.
-Mohit
|||
Jamie Thomson wrote: When the dll reference is added there is no option to browse and add assembly. It shows .NET assemblies which are from GAC out of which both the dll have this path: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies in the property box. This is on my development machine. But in dtsx file, it does not have any reference of this path. Below is the code generated used in production server:
<Reference
Name = "Microsoft.SqlServer.DTSPipelineWrap"
AssemblyName = "Microsoft.SqlServer.DTSPipelineWrap"
/>
<Reference
Name = "Microsoft.SQLServer.DTSRuntimeWrap"
AssemblyName = "Microsoft.SqlServer.DTSRuntimeWrap"
/>
I assume it is coming from GAC.
-Mohit
This "property box" that you speak of. What object does that contain the properties of? On my machine I check properties of Microsoft.SqlServer.DTSPipelineWrap in c:\windows\assembly and there is no mention of a path anywhere. I'm confused.
The .dtsx file will not contain paths to DLLs, that's the whole point of GACing them.
-Jamie
|||Finally I got resolution to this issue
Cause:
Security Update for Microsoft .Net Framework 2.0 (KB928365) is installed on the server which causes SSIS packages to fail (Specifically custom data flow tasks using GACed dll)
Resolution:
Install this KB: SQLServer2005-KB932557-x86-ENU.exe on the server.
Link: http://support.microsoft.com/kb/932557/
This KB while installing gives option to Install the patch only for SQL Server Integration Services 2005 and SQL Server Tools and Workstation Components 2005. This KB is not required to be installed for SQL Server Instances. (Restart not required)
Known Issue:
After installing this KB, few warnings may be displayed which are known issues with SSIS.
Thanks
Mohit
Subscribe to:
Posts (Atom)