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 5 (of 5 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: 246960 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.

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

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?