May 22, 2008 at 6:13 am
Hi Guys,
Was wondering if anyone can give me a little insight into a problem I've been having when importing XML via SSIS.
When using a standed XML source file and a generated XSD in SSIS I'm having problems accessing some of the data within the file. The top level attribute holds vital information which tells me for which date the data is valid for. Unfortunately I'm unable to get access to this piece of information.
I've created a test XML file: Not very imaginative, but it highlights my problem. (Had to name it Test.txt to pass the filters, just a plain test XML file).
If you select the attached XML file with the XML datasource (within a standard data task )and generate the XSD you'll notice you get access to all the data, i.e. it'll create the record sets which you'll then be able to transfter into the database. The problem I'm having is accessing the top level attribute, in this case it's the report date.
Maybe I need to change the XSD? Unfortunately changing the XML format to include the report date in a seperate element is not possible at the moment.
Any help would be greatly appreciated. If anyone needs anymore information just shout, hope I have included enough information for you guys to get a general idea of the problem.
Cheers
Ian
November 3, 2011 at 4:05 am
Dear all, I was wondering if anyone solved this problem?
I have a similar situation to this one: I need to capture the information from the upper level attribute.
For example, the Unit tag (in xml file) has multiple Payment tags in the lower level.
When I import the data with SSIS, I can see those payments in the Payments table, but, WITHOUT the information on the exact Unit ID where each payment has been made.
There is, also UNIT table in the database, but without any information in it I could use joins.
Is there a way to solve this problem?
Thanks in advance!
November 3, 2011 at 6:40 am
Using the above example, the generated xsd file needs another 'toplevel' element. Make it a complex type and sequence and then you will get a new table called report which has the reportdate attribute as a column.
Not sure if this the the right way to do it, but it seems to work.
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="toplevel">
<xs:complexType>
<xs:sequence>
<xs:element name="report">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="names">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="name">
<xs:complexType>
<xs:attribute name="id" type="xs:unsignedByte" use="optional" />
<xs:attribute name="name" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="addresses">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="address">
<xs:complexType>
<xs:attribute name="id" type="xs:unsignedByte" use="optional" />
<xs:attribute name="address" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="reportdate" type="xs:date" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
November 3, 2011 at 6:51 am
Thank you. I will try it and get back to you.
Best,
April 24, 2019 at 5:10 pm
saved much of my time, thanks for the reply @twillcomp
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply