none
XML Source Adapter Advanced Editor

    Question

  • 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.
    Tuesday, April 13, 2010 7:21 PM

All replies