Post reply

Importing XML Attributes via SSIS

  • 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

  • October 3, 2012 at 9:47 am

    #1545032

    can you plz share the sample file.

  • October 3, 2012 at 9:58 am

    #1545039

    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.

  • October 3, 2012 at 10:12 am

    #1545047

    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

  • October 7, 2012 at 4:58 pm

    #1546531

    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/

  • September 8, 2014 at 12:18 am

    #1743773

    Hi Dan,

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

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

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