SSIS XML Source Columns Don't Match XML/XSD

Question

SSIS XML Source Columns Don't Match XML/XSD

Old Hand

Points: 331

April 9, 2025 at 6:00 pm

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 5 months ago by s15199d. Reason: cleaned up formatting

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

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

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)?

s15199d Old Hand Points: 331 More actions · Answer 2

@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 5 months ago by s15199d.
This reply was modified 5 months ago by s15199d.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

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;