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>
April 10, 2025 at 9:45 am
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)?
April 10, 2025 at 12:53 pm
@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.
April 11, 2025 at 9:08 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy