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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy