CODEWITHSUNDEEP
sql-serverssissql-server-2012
Jānis
Jānis

Reputation: 2266

SSIS How to import xml elements into table

I am using XML source in SSIS to import XML file into SQL Server database.

I am not using all detail elements from XML file. But I want to save original element with all details in case they are needed at some point in future.

Lets say xml:

<root>
  <row>
    <desc>Some row</desc>
    <child>
      <hi>hi</hi>
      <ho>ho</ho>
    </child>
  </row>
  <row>
    <desc>Some row2</desc>
    <child>
      <hi>hi2</hi>
      <ho>ho2</ho>
    </child>
  </row>
</root>

Intended result in structure:

Create Table ParentTable
(
    Id int primary key identity,
    [desc] nvarchar(50),
    xmlElement xml
)

How can I load original XML element (in this case element "row") into database as well by using SSIS?

Upvotes: 1

Views: 2740

Answers (1)

Darka
Darka

Reputation: 2768

I am new in SSIS, but in internet found solution (maybe not the best but worked).

So here it comes.

  1. First i create same table as you provided ParentTable, just changed desc to 255. Also added Connection Manager to package.
  2. Created two new variables User::FileName = "some.xml" and User::SourceCatalog = "C:\xmlCatalog\"
  3. Then added Data Flow Task in which I added Script Component (selected Source type).
  4. Opened Script Transformation Editor in Script tab into ReadOnlyVariables property added newly created variables User::FileName,User::SourceCatalog.
  5. In the tab Inputs and Outputs renamed Output 0 to XMLResultOutput and under Output Columns created two new columns xmlDesc (Data Type = Unicode string [DT_WSTR] 255) and xmlData (Data Type = Unicode string [DT_WSTR] 3000). This variables will be used later in C# script.

inputs and outputs

  1. Pressed Edit Script... in the Script tab. In the opened window in the CreateNewOutputRows method paste this code:

    XmlDocument xDoc = new XmlDocument();

string xml_filepath = Variables.SourceCatalog + Variables.FileName;
xDoc.Load(xml_filepath);

foreach (XmlNode xNode in xDoc.SelectNodes("//row"))
{

    this.XMLResultOutputBuffer.AddRow();

    this.XMLResultOutputBuffer.xmlData = xNode.OuterXml.ToString();

    this.XMLResultOutputBuffer.xmlDesc = xNode.SelectSingleNode("./desc").InnerText;//xNode.ChildNodes[0].InnerText;

}

    Don't forget to add using System.Xml;

  2. Added OLE DB Destination component, linked Script Component to it, selected table, mapped columns and THATS IT.

Upvotes: 1

Related Questions