Importing XML Attributes via SSIS

Question

Post reply

Importing XML Attributes via SSIS

danchard

Ten Centuries

Points: 1051
More actions
October 3, 2012 at 9:18 am

#268742

Hello all.
I am currently in the process of attempting to load a number of XML files into SQL (using SSIS), and whilst things started off well, I’ve now run into a problem that I don’t seem to be able to get around. I’ve used a ForEach Loop with a Data Flow Task and set up the XML Source component without issue. I generated an XSD and was impressed (and surprised) when the package ran first time and populated my four destination tables exactly as I’d hoped it would.
However, there is a small amount of header data in each file which is stored as a set of attributes rather than elements and I don’t appear to be able to access this regardless of any fiddling with the XSD. I can’t make any manual changes to the XML files as this will form part of a regular process which will need to be automated.
Can anybody offer a solution to this as it doesn’t seem like a complicated problem, although I don’t know what else to try!
Many thanks in advance!
Dan

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

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

sumit.joshij Ten Centuries Points: 1040 More actions · Answer 1

sumit.joshij

Ten Centuries

Points: 1040

October 3, 2012 at 9:47 am

#1545032

can you plz share the sample file.

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

sumit.joshij (10/3/2012)
can you plz share the sample file.

+1

impossible to help with this without getting an idea of what the file looks like and what you need out of it.

danchard Ten Centuries Points: 1051 More actions · Answer 3

Hi guys,

Sample XML file below (this is just a sample - I have replaced the actual data but the layout of the file remains unchanged):

<?xml version="1.0" encoding="ISO-8859-1" ?>

<lxp xml_lang="en_US" start_date="2012-07-01" program_name="[snipped]" version_date_of_program="2012-01-01" filename="samplefile" serial_no="12345" character_set="UTF8" decimal_character="." date_format="YYYY-MM-DD" time_format="HH24:MI:SS" description="header record" xsi:noNamespaceSchemaLocation="something.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<data1>

<object>

<vin vin="1234567890abcdefg"/>

</object>

<damage>

<repair_date>2012-07-02</repair_date>

<service_no>11A1</service_no>

</damage>

</data1>

<data1>

<object>

<vin vin="2234567890abcdefg"/>

</object>

<damage>

<repair_date>2012-07-03</repair_date>

<service_no>22B2</service_no>

</damage>

</data1>

<data1>

<object>

<vin vin="3234567890abcdefg"/>

</object>

<damage>

<repair_date>2012-07-04</repair_date>

<service_no>33C3</service_no>

</damage>

</data1>

</lxp>

XSD file:

<?xml version="1.0"?>

<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="lxp">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" maxOccurs="unbounded" name="data1">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" name="object">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" name="vin">

<xs:complexType>

<xs:attribute name="vin" type="xs:string" use="optional" />

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element minOccurs="0" name="damage">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" name="repair_date" type="xs:date" />

<xs:element minOccurs="0" name="service_no" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

<xs:attribute name="xml_lang" type="xs:string" use="optional" />

<xs:attribute name="start_date" type="xs:date" use="optional" />

<xs:attribute name="program_name" type="xs:string" use="optional" />

<xs:attribute name="version_date_of_program" type="xs:date" use="optional" />

<xs:attribute name="filename" type="xs:string" use="optional" />

<xs:attribute name="serial_no" type="xs:unsignedShort" use="optional" />

<xs:attribute name="character_set" type="xs:string" use="optional" />

<xs:attribute name="decimal_character" type="xs:string" use="optional" />

<xs:attribute name="date_format" type="xs:string" use="optional" />

<xs:attribute name="time_format" type="xs:string" use="optional" />

<xs:attribute name="description" type="xs:string" use="optional" />

</xs:complexType>

</xs:element>

</xs:schema>

(the XSD was generated by SSIS - it is the attributes at the bottom that I am trying to access)

Please let me know if you require anything else!

Thanks,

Dan

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

Hi Dan,

You may have to use XSLT to transform the input XML document, before you process it with the XML Source component.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

reach_saugata SSC-Addicted Points: 443 More actions · Answer 5

Hi Dan,

I am facing this exact same problem. Any luck with this yet?