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

Question

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.

Answer 1

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" />

Answer 2

You can use the nodes() method to return a rowset of nodes in the xml document. This is the simplest example:

select node_table.xml_node_column.query('.') node 
from xmldocument
cross apply xmldocument.nodes('/root/RECORD') node_table(xml_node_column)

https://learn.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-ver16