{"id":13848,"date":"2022-10-13T06:29:04","date_gmt":"2022-10-13T04:29:04","guid":{"rendered":"https:\/\/expert-only.com\/?p=13848"},"modified":"2023-05-17T12:10:49","modified_gmt":"2023-05-17T10:10:49","slug":"import-xml-file-into-a-table-with-ssis","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/","title":{"rendered":"Import an XML file into a SQL table with SSIS"},"content":{"rendered":"\n<h4 class=\"wp-block-heading has-text-align-center\" id=\"h-tutorial-to-import-an-xml-file-into-a-sql-server-table-with-an-ssis-package\"><strong><em>Tutorial to import an XML file into a SQL Server table with an SSIS package.<\/em><\/strong><\/h4>\n\n\n\n<p>To import a file in XML format into a SQL Server table with SSIS, it is necessary to provide the schema in XSD format that represents the structure of the file. In this case, the XSD file is automatically generated by Visual Studio and Integration Services. This tutorial explains step by step how to import an XML file into a SQL table with the <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/integration-services\/data-flow\/xml-source?view=sql-server-ver16\" target=\"_blank\" rel=\"noreferrer noopener\">native XML Source component<\/a>.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#1-prepare-the-sql-table-with-ssms-and-the-xml-file-to-import\" >1. Prepare the SQL table with SSMS and the XML file to import<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#2-configure-the-xml-source-file-from-the-ssis-data-flow\" >2. Configure the XML source file from the SSIS Data Flow<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#3-ssis-error-no-maximum-length-was-specified-for-the-xml-source\" >3. SSIS error No maximum length was specified for the XML Source<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#4-configure-the-connection-to-the-destination-sql-table\" >4. Configure the connection to the destination SQL table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#5-run-the-package-to-import-the-xml-data-into-sql-server\" >5. Run the package to import the XML data into SQL Server<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1-prepare-the-sql-table-with-ssms-and-the-xml-file-to-import\"><\/span>1. Prepare the SQL table with SSMS and the XML file to import<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The destination table is the customer table with a basic structure. Here is the SQL Server code to create it from a connection to the target database with SSMS.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Create the client table\nCREATE TABLE [dbo].[Customers](\n   [CustomerID] [int] NOT NULL,\n   [FirstName] [nvarchar](20) NULL,\n   [LastName] [nvarchar](20) NULL,\n   [City] [nvarchar](20) NULL,\n   [Country] [nvarchar](50) NULL,\n   CONSTRAINT [CustomersPrimaryKeyCustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)\n);\nGO<\/pre>\n\n\n\n<p>Download the XML file used in this tutorial from the link below. Due to restrictions, the file is renamed to txt and with comments. So, rename the file to Customers_Data.csv and remove the comments, i.e. delete the first and last line of the file.<\/p>\n\n\n\n<div class=\"wp-block-file aligncenter\"><a id=\"wp-block-file--media-40a13148-2dd5-4119-a93e-a07adce29b54\" href=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/Customers_Data.txt\"><strong>Customers_Data.txt<\/strong><\/a><a href=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/Customers_Data.txt\" class=\"wp-block-file__button wp-element-button\" download aria-describedby=\"wp-block-file--media-40a13148-2dd5-4119-a93e-a07adce29b54\">Download the sample XML file<\/a><\/div>\n\n\n\n<p>In case you have trouble downloading and using the file, here is a sample of the file in XML format with only the first 6 lines.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"xml\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?>\n&lt;Customers>\n   &lt;Customer>\n      &lt;CustomerID>1&lt;\/CustomerID>\n      &lt;FirstName>Ali&lt;\/FirstName>\n      &lt;LastName>Ahmed&lt;\/LastName>\n      &lt;City>Cairo&lt;\/City>\n      &lt;Country>Egypt&lt;\/Country>\n   &lt;\/Customer>\n   &lt;Customer>\n      &lt;CustomerID>2&lt;\/CustomerID>\n      &lt;FirstName>Johnny&lt;\/FirstName>\n      &lt;LastName>John&lt;\/LastName>\n      &lt;City>Toronto&lt;\/City>\n      &lt;Country>Canada&lt;\/Country>\n   &lt;\/Customer>\n   &lt;Customer>\n      &lt;CustomerID>3&lt;\/CustomerID>\n      &lt;FirstName>John&lt;\/FirstName>\n      &lt;LastName>Doe&lt;\/LastName>\n      &lt;City>Mexico City&lt;\/City>\n      &lt;Country>Mexico&lt;\/Country>\n   &lt;\/Customer>\n   &lt;Customer>\n      &lt;CustomerID>4&lt;\/CustomerID>\n      &lt;FirstName>Shu&lt;\/FirstName>\n      &lt;LastName>Abbas&lt;\/LastName>\n      &lt;City>Paris&lt;\/City>\n      &lt;Country>France&lt;\/Country>\n   &lt;\/Customer>\n   &lt;Customer>\n      &lt;CustomerID>5&lt;\/CustomerID>\n      &lt;FirstName>Jeane&lt;\/FirstName>\n      &lt;LastName>Raffin&lt;\/LastName>\n      &lt;City>Liushutun&lt;\/City>\n      &lt;Country>China&lt;\/Country>\n   &lt;\/Customer>\n   &lt;Customer>\n      &lt;CustomerID>6&lt;\/CustomerID>\n      &lt;FirstName>Legra&lt;\/FirstName>\n      &lt;LastName>Leate&lt;\/LastName>\n      &lt;City>B\u0142aszki&lt;\/City>\n      &lt;Country>Poland&lt;\/Country>\n   &lt;\/Customer>\n&lt;\/Customers><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2-configure-the-xml-source-file-from-the-ssis-data-flow\"><\/span>2. Configure the XML source file from the SSIS Data Flow<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>First <a href=\"https:\/\/expert-only.com\/en\/ssis\/create-ssis-project-visual-studio-2019\/\">create an SSIS package in a project<\/a>, and <a href=\"https:\/\/expert-only.com\/en\/ssis\/create-a-simple-ssis-data-flow\/\">add a data flow<\/a> into it. Then add the <em>XML Source<\/em> and <em>OLE DB Destination<\/em> components to the SSIS data flow.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"640\" height=\"600\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-1-add-xml-source-ole-db-destination.jpg\" alt=\"Use the XML Source and the OLE DB Destination SSIS components to import the XML file into a table\" class=\"wp-image-12717\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-1-add-xml-source-ole-db-destination.jpg 640w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-1-add-xml-source-ole-db-destination-300x281.jpg 300w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><figcaption class=\"wp-element-caption\">Use the XML Source and the OLE DB Destination SSIS components to import the XML file<\/figcaption><\/figure><\/div>\n\n\n<p>Open the XML component and point to the XML file.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Select the path of the XML file to import: <strong>C:\\data\\Customers_Data.xml<\/strong><\/li>\n\n\n\n<li>Use Generate XSD and choose the path of the file: <strong>C:\\data\\Customers_Data_VisualStudio.xsd<\/strong><\/li>\n\n\n\n<li>Click on <em>Columns<\/em> to check the mapping<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"830\" height=\"440\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-2-select-source-generate-xsd-schema-1.jpg\" alt=\"Set up the source XML file to be imported into the database and the XSD schema with SSIS\" class=\"wp-image-12735\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-2-select-source-generate-xsd-schema-1.jpg 830w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-2-select-source-generate-xsd-schema-1-300x159.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-2-select-source-generate-xsd-schema-1-768x407.jpg 768w\" sizes=\"auto, (max-width: 830px) 100vw, 830px\" \/><figcaption class=\"wp-element-caption\">Set up the source XML file to be imported into the database and the XSD schema with SSIS<\/figcaption><\/figure><\/div>\n\n\n<p>The XSD schema does not indicate the length of the fields by default and the package therefore assigns the DT_WSTR type with a length of 255 characters. This is the SSIS data type (DT_WSTR,255). The package therefore displays this warning message.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"809\" height=\"572\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-3-no-maximum-length-error.jpg\" alt=\"SSIS Error No maximum length was specified for the XML Source\" class=\"wp-image-12742\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-3-no-maximum-length-error.jpg 809w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-3-no-maximum-length-error-300x212.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-3-no-maximum-length-error-768x543.jpg 768w\" sizes=\"auto, (max-width: 809px) 100vw, 809px\" \/><figcaption class=\"wp-element-caption\">SSIS Error No maximum length was specified for the XML Source<\/figcaption><\/figure><\/div>\n\n\n<p>Warning at {EF1A3256-9D27-4D85-9F3A-570513FF215F} [XML Source [69]]: No maximum length was specified for the XML Source.Outputs[Customer].Columns[FirstName] with external data type System.String. The SSIS Data Flow Task data type &#8220;DT_WSTR&#8221; with a length of 255 will be used.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3-ssis-error-no-maximum-length-was-specified-for-the-xml-source\"><\/span>3. SSIS error No maximum length was specified for the XML Source<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>This is only a warning message, but it is possible to correct the problem. To do this, modify the XSD file and specify the lengths for all text fields. The XSD file generated automatically by Visual Studio is of the following type. <\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1000\" height=\"560\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-4-generated-xsd-file.jpg\" alt=\"Automatically generated XSD schema file in Visual Studio XML Source\" class=\"wp-image-12749\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-4-generated-xsd-file.jpg 1000w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-4-generated-xsd-file-300x168.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-4-generated-xsd-file-768x430.jpg 768w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-4-generated-xsd-file-678x381.jpg 678w\" sizes=\"auto, (max-width: 1000px) 100vw, 1000px\" \/><figcaption class=\"wp-element-caption\">Automatically generated XSD schema file in Visual Studio XML Source<\/figcaption><\/figure><\/div>\n\n\n<p>The XML element line does not contain the <strong>maxLength<\/strong> property. Replace each such line:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"xml\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">&lt;xs:element minOccurs=\"0\" name=\"FirstName\" type=\"xs:string\" \/><\/pre>\n\n\n\n<p>With a block with the explicit length of the text field, for example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"xml\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">&lt;xs:element minOccurs=\"0\" name=\"FirstName\" >\n &lt;xs:simpleType>\n   &lt;xs:restriction base=\"xs:string\">\n     &lt;xs:minLength value=\"0\"\/>\n     &lt;xs:maxLength value=\"20\"\/>\n   &lt;\/xs:restriction>\n &lt;\/xs:simpleType>\n&lt;\/xs:element><\/pre>\n\n\n\n<p>The result is a file with the corresponding length for each column in the SQL table. So, the modified file shows the length of each XML text field, as in the picture below.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"990\" height=\"650\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-5-update-xsd-schema-add-length.jpg\" alt=\"Update the XSD schema file with the maxLength property\" class=\"wp-image-12758\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-5-update-xsd-schema-add-length.jpg 990w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-5-update-xsd-schema-add-length-300x197.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-5-update-xsd-schema-add-length-768x504.jpg 768w\" sizes=\"auto, (max-width: 990px) 100vw, 990px\" \/><figcaption class=\"wp-element-caption\">Update the XSD schema file with the maxLength property<\/figcaption><\/figure>\n\n\n\n<p>Save the new XSD file as <strong>C:\\dataCustomers_Data.xsd<\/strong>. Finally point the SSIS XML component to the new and modified XML schema.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"440\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-6-setup-xsd-schema.jpg\" alt=\"Configure the XML file to be imported with SSIS with the new XSD file\" class=\"wp-image-12765\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-6-setup-xsd-schema.jpg 700w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-6-setup-xsd-schema-300x189.jpg 300w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Configure the XML file to be imported with SSIS with the new XSD file<\/figcaption><\/figure><\/div>\n\n\n<p>Once the warning is corrected, check the mapping of the columns in the source component.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"540\" height=\"580\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-7-check-xml-mapping.jpg\" alt=\"Check the columns of the XML file to be imported with SSIS\" class=\"wp-image-12771\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-7-check-xml-mapping.jpg 540w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-7-check-xml-mapping-279x300.jpg 279w\" sizes=\"auto, (max-width: 540px) 100vw, 540px\" \/><figcaption class=\"wp-element-caption\">Check the columns of the XML file to be imported with SSIS<\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"4-configure-the-connection-to-the-destination-sql-table\"><\/span>4. Configure the connection to the destination SQL table<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Open the <em>OLE DB Destination<\/em> component and choose the table in which to import the XML file. This is the <em>dbo.Customers<\/em> table created in step 1 of this tutorial.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"680\" height=\"480\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-8-set-up-target-table.jpg\" alt=\"Choose the target table to import the customers XML file\" class=\"wp-image-12778\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-8-set-up-target-table.jpg 680w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-8-set-up-target-table-300x212.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-8-set-up-target-table-678x480.jpg 678w\" sizes=\"auto, (max-width: 680px) 100vw, 680px\" \/><figcaption class=\"wp-element-caption\">Choose the target table to import the customers XML file<\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5-run-the-package-to-import-the-xml-data-into-sql-server\"><\/span>5. Run the package to import the XML data into SQL Server<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Finally, run the package and check that the data is inserted into the table without error with an SQL query from SSMS.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-9-execute-file-import.jpg\" alt=\"Run the SSIS package and import the XML file into the SQL Server database\" class=\"wp-image-12784\" width=\"580\" height=\"540\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-9-execute-file-import.jpg 580w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-import-xml-file-9-execute-file-import-300x279.jpg 300w\" sizes=\"auto, (max-width: 580px) 100vw, 580px\" \/><figcaption class=\"wp-element-caption\">Run the SSIS package and import the XML file into the SQL Server database<\/figcaption><\/figure><\/div>\n\n\n<p>Use this <a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-insert-into-from-a-select\/\">SQL SELECT<\/a> query to read the imported data from an SSMS connection.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT *\nFROM [dbo][Customers];\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion on XML file import into SQL Server with SSIS<\/h3>\n\n\n\n<p>The XML Source component allows you to import an XML file into a MS SQL table with SSIS. The component is easy to use, especially with the option to automatically generate the XSD schema directly from the XML file provided. However, to work in control and avoid warnings and truncations, make manual adjustments, especially on the length of text fields.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Other tutorials on data integration with SSIS<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/expert-only.com\/en\/ssis\/import-text-file-into-sql-server-database-with-ssis\/\">Import a CSV file with SSIS<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/ssis\/import-excel-file-into-sql-table-with-ssis\/\">Import an Excel file with SSIS<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/ssis\/import-multiple-text-files-with-ssis\/\">Tutorial to import multiple CSV files with SSIS<\/a><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\nhttps:\/\/expert-only.com\/en\/ssis-tutorials\/\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>Tutorial to import an XML file into a SQL Server table with an SSIS package. To import a file in XML format into a SQL Server table with SSIS, it is necessary to provide the schema in XSD format <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/\" title=\"Import an XML file into a SQL table with SSIS\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":10271,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[516],"tags":[],"class_list":{"0":"post-13848","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-ssis"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Import an XML file into a SQL table with SSIS - Integration Services<\/title>\n<meta name=\"description\" content=\"To import a file in XML format into a SQL Server table with an SSIS package, use the XML Source component and the OLE DB connection.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Import an XML file into a SQL table with SSIS\" \/>\n<meta property=\"og:description\" content=\"To import a file in XML format into a SQL Server table with an SSIS package, use the XML Source component and the OLE DB connection.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-13T04:29:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-05-17T10:10:49+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Import an XML file into a SQL table with SSIS\",\"datePublished\":\"2022-10-13T04:29:04+00:00\",\"dateModified\":\"2023-05-17T10:10:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/\"},\"wordCount\":844,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg\",\"articleSection\":[\"SSIS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/\",\"url\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/\",\"name\":\"Import an XML file into a SQL table with SSIS - Integration Services\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg\",\"datePublished\":\"2022-10-13T04:29:04+00:00\",\"dateModified\":\"2023-05-17T10:10:49+00:00\",\"description\":\"To import a file in XML format into a SQL Server table with an SSIS package, use the XML Source component and the OLE DB connection.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Import an XML file into a SQL table with SSIS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Import an XML file into a SQL table with SSIS - Integration Services","description":"To import a file in XML format into a SQL Server table with an SSIS package, use the XML Source component and the OLE DB connection.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/","og_locale":"en_US","og_type":"article","og_title":"Import an XML file into a SQL table with SSIS","og_description":"To import a file in XML format into a SQL Server table with an SSIS package, use the XML Source component and the OLE DB connection.","og_url":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-10-13T04:29:04+00:00","article_modified_time":"2023-05-17T10:10:49+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Import an XML file into a SQL table with SSIS","datePublished":"2022-10-13T04:29:04+00:00","dateModified":"2023-05-17T10:10:49+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/"},"wordCount":844,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg","articleSection":["SSIS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/","url":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/","name":"Import an XML file into a SQL table with SSIS - Integration Services","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg","datePublished":"2022-10-13T04:29:04+00:00","dateModified":"2023-05-17T10:10:49+00:00","description":"To import a file in XML format into a SQL Server table with an SSIS package, use the XML Source component and the OLE DB connection.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/matrix-69E193580C5_1920x1080.jpg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/ssis\/import-xml-file-into-a-table-with-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Import an XML file into a SQL table with SSIS"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/13848","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=13848"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/13848\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/10271"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=13848"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=13848"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=13848"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}