SSIS XML Source Columns Don't Match XML/XSD

  • In my SSIS package I have a Data Flow with a XML Source with the below XSD. The problem I have is when I try to do a Data Conversion and subsequently an OLE DB Destination, the columns available for PortfolioExport__PortfolioData__OfficeParks__OfficePark__Properties are Properties_Id and OfficePark_Id. What I need are the OfficePark.Id and the Properties.SourceId. I tried it with the available OfficePark_Id and Properties_Id columns to see if it was the right data with the wrong name, but it was not.

    How do I get it to let me choose the OfficePark.Id and the Properties.SourceId?

    Note: I'm using object.column nomenclature here...I'm not sure the correct naming convention.

    XSD Excerpt:

    <?xml version="1.0"?>
    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="PortfolioExport">
        <xs:complexType>
          <xs:sequence>
            <xs:element minOccurs="0" name="jobid" type="xs:string" />
            <xs:element minOccurs="0" name="PortfolioData">
              <xs:complexType>
                <xs:sequence>
                  <xs:element minOccurs="0" name="OfficeParks">
                    <xs:complexType>
                      <xs:sequence>
                        <xs:element minOccurs="0" maxOccurs="unbounded" name="OfficePark">
                          <xs:complexType>
                            <xs:sequence>
                              <xs:element minOccurs="0" name="Id" type="xs:unsignedInt" />
                              <xs:element minOccurs="0" name="Name" type="xs:string" />
                              <xs:element minOccurs="0" name="SourceID" type="xs:string" />
                              <xs:element minOccurs="0" name="CreatedDate" type="xs:dateTime" />
                              <xs:element minOccurs="0" name="Properties">
                                <xs:complexType>
                                  <xs:sequence>
                                    <xs:element minOccurs="0" maxOccurs="unbounded" name="SourceId" type="xs:string" />
                                  </xs:sequence>
                                </xs:complexType>
                              </xs:element>
                            </xs:sequence>
                          </xs:complexType>
                        </xs:element>
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
    ***THERE'S MORE HERE REDACTED FOR SUCCINCTNESS***
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>

    • This topic was modified 2 months, 1 week ago by s15199d. Reason: cleaned up formatting
  • If you import the data into a table using all of the available outputs, is the data you require imported as part of that (contained in one of the imported columns)?


  • @PhilParkin thanks for your response.  The data I need exists in the source XML.

    Here's a sample of the XML.

    <?xml version="1.0" encoding="UTF-8"?>
    <PortfolioExport>
      <jobid>c0673dd0a2384799</jobid>
      <PortfolioData>
      <OfficeParks>
        <OfficePark>
          <Id>71150</Id>
          <Name>Name Goes Here</Name>
          <SourceID>Name Repeated Here</SourceID>
          <CreatedDate>2021-01-20T09:04:07Z</CreatedDate>
          <Properties>
            <SourceId>Option C</SourceId>
            <SourceId>Option A</SourceId>
            <SourceId>Option B</SourceId>
            <SourceId>Option D</SourceId>
          </Properties>
        </OfficePark>
        ***MORE OfficePark OBJECTS HERE***
      </OfficeParks>
      </PortfolioData>
    </PortfolioExport>

    I'm trying to put ID, Name, SourceId, CreatedDate into an Office_Park table in SQL.  I've got that part working.

    Then I'm trying to put OfficePark.ID, OfficePark.Properties.SourceId into an Office_Park_Properties table in SQL. This is where I'm presently stuck.

    Unfortunately I don't control the production of the XML. I'm a consumer of this data.

    • This reply was modified 2 months, 1 week ago by s15199d.
    • This reply was modified 2 months, 1 week ago by s15199d.
  • In case it's useful, here's an example of handling this in T-SQL. Note that I had to remove the XML encoding, as that was giving an error.

    DECLARE @x NVARCHAR(MAX);
    DECLARE @DocHandle INT;

    SET @x
    = N'<?xml version="1.0"?>
    <PortfolioExport>
    <jobid>c0673dd0a2384799</jobid>
    <PortfolioData>
    <OfficeParks>
    <OfficePark>
    <Id>71150</Id>
    <Name>Name Goes Here</Name>
    <SourceID>Name Repeated Here</SourceID>
    <CreatedDate>2021-01-20T09:04:07Z</CreatedDate>
    <Properties>
    <SourceId>Option C</SourceId>
    <SourceId>Option A</SourceId>
    <SourceId>Option B</SourceId>
    <SourceId>Option D</SourceId>
    </Properties>
    </OfficePark>
    <OfficePark>
    <Id>71151</Id>
    <Name>Name 2 Goes Here</Name>
    <SourceID>Name 2 Repeated Here</SourceID>
    <CreatedDate>2022-01-20T09:04:07Z</CreatedDate>
    <Properties>
    <SourceId>Option Z</SourceId>
    <SourceId>Option Y</SourceId>
    <SourceId>Option X</SourceId>
    <SourceId>Option W</SourceId>
    </Properties>
    </OfficePark>
    </OfficeParks>
    </PortfolioData>
    </PortfolioExport>';

    -- Create an internal representation of the XML document.
    EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT, @x;

    SELECT *
    FROM
    OPENXML (@DocHandle, N'/PortfolioExport/PortfolioData/OfficeParks/OfficePark/Properties/SourceId')
    WITH
    (
    Id INT '../../Id'
    ,Name VARCHAR (100) '../../Name'
    ,CreatedDate DATETIME '../../CreatedDate'
    ,SourceId VARCHAR (50) '.'
    );

    EXEC sys.sp_xml_removedocument @DocHandle;

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply