XML Source Adapter Advanced Editor

• Tuesday, April 13, 2010 7:21 PM

   

   
  

  0

  BIDS Version 10.0.2531.0 project: read an XML file, save some elements to 1 Table.

  XML (sensitive data edited with ellipse...)

  <...ServicesResponse country="USA" language="ENG" marketid="1">
  <Status desc="Web service status OK" id="VI2000"/>
  <Vehicles>
   <Vehicle vin="2LM123...78901233">
    <Status desc="Vehicle status OK" id="VI1000"/>
     <VehicleAttributes>
      <VehicleAttribute VehicleType="Truck" valueid="4673"/>
      <VehicleAttribute Year="2008" valueid="4285"/>
      <VehicleAttribute BodyStyle="Sport Utility Vehicle" valueid="4698"/>
      <VehicleAttribute Make="Lincoln" valueid="185"/>
      <VehicleAttribute Model="MKX" valueid="4240"/>
      <VehicleAttribute Series="" valueid="105"/>
     </VehicleAttributes>
   </Vehicle>
   <Vehicle vin="2LM123...78901234">
    <Status desc="Vehicle status OK" id="VI1000"/>
     <VehicleAttributes>
      <VehicleAttribute VehicleType="Truck" valueid="4673"/>
      <VehicleAttribute Year="2007" valueid="4093"/>
      <VehicleAttribute BodyStyle="Sport Utility Vehicle" valueid="4698"/>
      <VehicleAttribute Make="Lincoln" valueid="185"/>
      <VehicleAttribute Model="MKX" valueid="4240"/>
      <VehicleAttribute Series="" valueid="105"/>
     </VehicleAttributes>
   </Vehicle>
  </Vehicles>
  </...ServicesResponse>
  
  

  XSD:

  <?xml version="1.0"?>
  <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="...ServicesResponse">
      <xs:complexType>
        <xs:sequence>
          <xs:element minOccurs="0" name="Status">
            <xs:complexType>
              <xs:attribute name="desc" type="xs:string" use="optional" />
              <xs:attribute name="id" type="xs:string" use="optional" />
            </xs:complexType>
          </xs:element>
          <xs:element minOccurs="0" name="Vehicles">
            <xs:complexType>
              <xs:sequence>
                <xs:element minOccurs="0" maxOccurs="unbounded" name="Vehicle">
                  <xs:complexType>
                    <xs:sequence>
                      <xs:element minOccurs="0" name="Status">
                        <xs:complexType>
                          <xs:attribute name="desc" type="xs:string" use="optional" />
                          <xs:attribute name="id" type="xs:string" use="optional" />
                        </xs:complexType>
                      </xs:element>
                      <xs:element minOccurs="0" name="VehicleAttributes">
                        <xs:complexType>
                          <xs:sequence>
                            <xs:element minOccurs="0" maxOccurs="unbounded" name="VehicleAttribute">
                              <xs:complexType>
                                <xs:attribute name="VehicleType" type="xs:string" use="optional" />
                                <xs:attribute name="valueid" type="xs:unsignedShort" use="optional" />
                                <xs:attribute name="Year" type="xs:unsignedShort" use="optional" />
                                <xs:attribute name="BodyStyle" type="xs:string" use="optional" />
                                <xs:attribute name="Make" type="xs:string" use="optional" />
                                <xs:attribute name="Model" type="xs:string" use="optional" />
                                <xs:attribute name="Series" type="xs:string" use="optional" />
                              </xs:complexType>
                            </xs:element>
                          </xs:sequence>
                        </xs:complexType>
                      </xs:element>
                    </xs:sequence>
                    <xs:attribute name="vin" type="xs:string" use="optional" />
                  </xs:complexType>
                </xs:element>
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:sequence>
        <xs:attribute name="country" type="xs:string" use="optional" />
        <xs:attribute name="language" type="xs:string" use="optional" />
        <xs:attribute name="marketid" type="xs:unsignedByte" use="optional" />
      </xs:complexType>
    </xs:element>
  </xs:schema>

  I need VIN, and everything inside VehicleAttributes.  In my data flow I have an XML Source adapter and a Merge Join. 

  In XML Source > Edit > Columns, what do I use for Output Name?  [I tried Vehicles]

  In XML Source Advanced Editor > Input and Output Properties, what Inputs need IsSorted = True [hm, can't paste an image] and which "_ID" output columns do I set SortKeyPosition=1? 

  When I drag an output from XML Source to Merge Join I'm prompted for Output and Input.  What do I select for Output (there are as many as I specify in the XML Source adapter)?

  I've tried many different combinations and can't seem to get lucky. 

  ---

  I imitated http://blogs.msdn.com/mattm/archive/2007/12/11/using-xml-source.aspx but my schema is a bit more complex.
  • Reply
  • Quote