1

I am trying to load multiple XML files into a SQL Server database. Aiming to insert XML file name (three files item1.xml, item2.xml and item3.xml and xsd are attached) as an additional column along with other column for each file.

I set up three variables enter image description here and setup the foreach loop editor enter image description here. It loaded each records from three files into a table

enter image description here.

I attempted to use derived column transformation and file path variable which is holding the file into the expression but that throwing me error

enter image description here

enter image description here .

Not sure what is wrong. I have attached all the steps for review including files. any help is highly appreciated. Thanks

--Item1.xml
<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <productnumber>I1145343</productnumber>
        <productname>Mousewire</productname>
        <Price>19.99</Price>
    </Item>
</Items>

--Item2.xml
<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <productnumber>I1145300</productnumber>
        <productname>Apple</productname>
        <Price>29.99</Price>
    </Item>
</Items>

--item3.xml
<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <productnumber>I1145387</productnumber>
        <productname>Samsung</productname>
        <Price>49.00</Price>
    </Item>
</Items>

XSD file

<xsd:schema xmlns:schema="ItemsXSDSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="ItemsXSDSchema" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Items">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element minOccurs="0" maxOccurs="unbounded" name="Item">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="Id" type="sqltypes:int" />
                            <xsd:element name="productnumber">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="20" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="productname">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="60" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="Price">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:numeric">
                                        <xsd:totalDigits value="18" />
                                        <xsd:fractionDigits value="2" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>
Improve this question
2
  • 1
    Your derived column has a length of zero. SSIS attempts to identify the length based on the variable value, since it's empty you get 0. That is what is probably throwing the error. Right click on the derived column, Advanced editor, input and output properties tab. Expand the Output columns, select your column and to the right update length to something like 255 or a value big enough to hold the fully path. Commented Dec 10, 2020 at 20:17
  • Provide an initial value for @[User::filePath] that approximates the expected run-time length. It is as Tim Mylott indicates, the metadata when the column was defined was length 0 so when the package actually runs and the value of filePath isn't the empty string, it blows up as you've overflowed the length of the column Commented Dec 11, 2020 at 4:34

1 Answer 1

Reset to default
0

You just want to store data as XML in a SQL table? If so, there is a data type for that. As long as the XML is valid, it can be saved.

NOTE: Be careful on XML as a datatype if you are going to do heavy searching on the field. While it is possible, certain types of searches can be very expensive.

EDIT: To insert XML as data. The way I have always done it is OPENROWSET. This turns XML into a "table" so you can insert, query, etc.

Here is another way someone did it.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@ Gregory A Beamer. You just want to store data as XML in a SQL table? NO xml data in a table as shown in the image. so need to add additional column next to price FileName which should come out as in row 1 (item1) in row 2(item2 and in row 3 (item3). Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.