Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  XMLBulkLoad Import and Mapping Issue

Author  Topic  kfischer Starting Member 1 Post Posted - 2009-12-21 : 17:01:07 Hi, I am having an issue with mapping an XMLBulkLoad Import. The import generally works fine, however the issue relates to the EDMPROP Node as shown in the XML file below. This node has both attribute values and an element value. I can get the values of the attributes mapped to a field, but I am unable to map the value of the element itself to a field because I am already identifying the elements sql relationship. Basically I am able to get everything mapped but the actual element values of the EDMPROP. Any help would be appreciated. Both the xml and the schema are shown below. Thanks in advance! First here is a sample of my xml file: <CU_TYPE ProgID="mmFramework.D8CuType.1" CUType="CUCustomSystem" Owner="System"> <DESCRIPTION>1100 - OH Transformers</DESCRIPTION> <CU ProgID="mmDesktop.MMCompatibleUnit.1"> <CUNAME>T2</CUNAME> <DESCRIPTION>2 Unit OH Transformer Make Up, Delta, Crossarm Construction</DESCRIPTION> <WMS_CODE>T2</WMS_CODE> <TABLENAME>gis.ELEC.ePoleFraming</TABLENAME> <SUBTYPE>0</SUBTYPE> <AVAILABLEWORKFUNCTIONS>107</AVAILABLEWORKFUNCTIONS> <WORK_FUNCTION>0</WORK_FUNCTION> <WF_STATUS>0</WF_STATUS> <QUANTITY>1</QUANTITY> <UNIT_OF_MEASURE>0</UNIT_OF_MEASURE> <EDM> <EDMPROP Name="LABOR_TYPE" Type="1">0</EDMPROP> <EDMPROP Name="ENERGIZED_STATUS" Type="1">0</EDMPROP> <EDMPROP Name="TRUCK_ACCESS" Type="1">Y</EDMPROP> <EDMPROP Name="DISTRIBUTION_UNDERBUILD" Type="1">Y</EDMPROP> <EDMPROP Name="CPU_BETTERMENT" Type="1">N</EDMPROP> <EDMPROP Name="QUANTITY" Type="1">1</EDMPROP> </EDM> </CU> Now here is my schema: <?xml version="1.0"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:xml:datatypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" > <xs:annotation> <xs:appinfo> <sql:relationship name="CU_Description" parent="cu_type" parent-key="description" child="cu" child-key="cu_type" /> <sql:relationship name="ExtendedData" parent="cu" parent-key="wms_code" child="cu_edm" child-key="cu_id" /> </xs:appinfo> </xs:annotation> <xs:element name="CU_TYPE" sql:relation="cu_type" sql:key-fields="description"> <xs:complexType> <xs:sequence> <xs:element name="DESCRIPTION" dt:type="string" sql:field="description" > </xs:element> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="CU" sql:relation="cu" sql:key-fields="wms_code" sql:relationship="CU_Description"> <xs:complexType> <xs:sequence> <xs:element name="WMS_CODE" dt:type="string" sql:field="wms_code" /> <xs:element name="CUNAME" dt:type="string" sql:field="cu_name" /> <xs:element name="DESCRIPTION" dt:type="string" sql:field="description" /> <xs:element name="TABLENAME" dt:type="string" sql:field="table_name" /> <xs:element name="SUBTYPE" dt:type="string" sql:field="subtype" /> <xs:element name="AVAILABLEWORKFUNCTIONS" dt:type="string" sql:field="availableworkfunctions" /> <xs:element name="WORK_FUNCTION" dt:type="string" sql:field="work_functions" /> <xs:element name="WF_STATUS" dt:type="string" sql:field="wf_status" /> <xs:element name="QUANTITY" dt:type="string" sql:field="quantity" /> <xs:element name="EDM" sql:is-constant="1"> <xs:complexType> <xs:sequence> <xs:element name="EDMPROP" sql:relation="cu_edm" dt:type="string" sql:key-fields="cu_id" sql:relationship="ExtendedData" sql:field="value" > <xs:complexType> <xs:attribute name="Name" dt:type="string" sql:field="name"></xs:attribute> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>