sqlxmlssisxml-parsingxmlnodelist

Saving all nodes from a big XML to SQL Database table in XMLType column using SSIS efficiently


I have a XML with many records as nodes in it. I need to save each record in xml format a SQL server table in column of XML datatype .

I can perform this task in SSIS using "XML Task Editor" to count all the nodes and using "For Loop Container" and read Node value using "XML Task Editor" and save it database.

Another option is using Script task, reading the XML file and save each node in a loop.

Please suggest a better approach which is efficient with big files.

Below is sample of Input XML File. I need to save each (3 records in below example) "RECORD" full node in XML form in SQL Server database table which has a column with xml datatype.

enter image description here


Solution

  • I would suggest 2 step approach.

    1. Use SSIS Import Column Transformation in a Data Flow Task to load entire XML file into a staging table single row column.
    2. Use stored procedure to produce individual RECORD XML fragments as separate rows and INSERT them into a final destination table.

    SQL

    DECLARE @staging_tbl TABLE (id INT IDENTITY PRIMARY KEY, xmldata XML);
    INSERT INTO @staging_tbl (xmldata) VALUES
    (N'<root>
        <RECORD UI="F298AF1F"></RECORD>
        <RECORD UI="4C6AAA65"></RECORD>
    </root>');
    
    -- INSERT INTO destination_table (ID, xml_record)
    SELECT id   
        , c.query('.') AS xml_record
    FROM @staging_tbl
    CROSS APPLY xmldata.nodes('/root/RECORD') AS t(c);
    

    Output

    id xml_record
    1 <RECORD UI="F298AF1F" />
    1 <RECORD UI="4C6AAA65" />