SSIS XML Parsing Error with Parent/Child Elements with Same Name

Question

Post reply

SSIS XML Parsing Error with Parent/Child Elements with Same Name

Owen White

Ten Centuries

Points: 1250
More actions
June 17, 2020 at 9:11 pm

#3764013
I am having a problem parsing an XML file to MS SQL staging tables via SSIS due to the XSD file structure which I may not change. Here is the XSD format:
```
<xs:element name="syndromes">
  <xs:complexType>
    <xs:sequence>
      <xs:element name="syndrome" maxOccurs="unbounded">
        <xs:complexType>
          <xs:sequence>
            <xs:element name="syndromeid" type="xs:integer"/>
            <xs:element name="uniqueid" type="xs:integer"/>
            <xs:element name="syndrome" type="xs:integer" minOccurs="0"/>
            <xs:element name="syndromespec" type="xs:string" minOccurs="0"/>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:sequence>
  </xs:complexType>
</xs:element>
```
I have mapped all of my table and setup an SSIS package to import the data from the XML into some staging tables. When I run the SSIS package it immediately fails and states the following:
[ABC_TEST_XML [2822]] Error: The ABC_TEST_XML was unable to process the XML data. The element "syndrome" cannot contain a child element. Content model is text only.
After doing some research, it looks as though SSIS can't handle the two elements "syndrome" with the same name. The XSD is able to allow me to setup all of my column mappings so I do not understand why when parsing the XML file it fails. Is there any work around? Is there a way to transform the data in the file prior to parsing. Any help would be greatly appreciated.
I am not allowed to change the XSD nor am I allowed to instruct the sender of the data to change the source XML file. I have to be able to parse the data as it is against the existing XSD.
Thank you in advance.
Owen White

Owen White

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

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

TangoVictor SSChampion Points: 10975 More actions · Answer 1

What about .Net? Could you do something in a script component source maybe?

https://docs.microsoft.com/en-us/dotnet/api/system.xml.serialization.xmlserializer.deserialize?view=netcore-3.1

https://docs.microsoft.com/en-us/dotnet/framework/wcf/feature-details/importing-schema-to-generate-classes

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

Can you take the XSD out of the equation and parse the source XML directly, using T-SQL (after importing the XML into a staging table)?

Or, as suggested above, a Script Component seems tidier.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Owen White Ten Centuries Points: 1250 More actions · Answer 3

I would prefer not take the XSD out of the equation but I can try that. The XSD ensures the incoming files meet the requirements of the data collection. The goal is to implement a timed data import by using a foreach loop container through files in a folder. SSIS will process each file and then import the data to staging tables. Then a different nightly batch will move all records from staging to production. I will see if I can do this without the XSD and let you know what I find.

Owen White

Owen White Ten Centuries Points: 1250 More actions · Answer 4

The issue seems to be fixed in later versions of SSIS/Visual Studio. My current version of Visual Studio was 2012. Once I upgraded to VS 2019, the issue no longer exists. Thank you for your suggestions.

Owen White