Included in SSIS PowerPack

SSIS XML Parser Transform

Parse an XML string column into multiple rows and columns in the Data Flow.

SSIS XML Parser Transform parses an XML string into multiple columns and rows. It is useful when raw XML is stored in a database column or arrives from another source, such as an API response, and you need it as relational data.

The trial includes XML Parser Transform and every other PowerPack component

XML Parser Transform in SSDT
XML Parser Transform - Parse XML Document, Convert XML to CSV or save to RDBMS (e.g. SQL Server, MySQL)
Parse an XML column into rows, then save to CSV or SQL ServerExplore the UI →

Trusted by data teams worldwide

MicrosoftIBMOracleDell TechnologiesHPCiscoAT&TVerizonDHLFedExUPSWalmart

Also rated on Capterra★★★★★ , Trustpilot★★★★★ and BBBA+ — see why teams trust ZappySys

At a glance

What XML Parser Transform can do

SSIS XML Parser Transform parses an XML string into multiple columns and rows. It is useful when raw XML is stored in a database column or arrives from another source, such as an API response, and you need it as relational data.

XML string to rows & columns

Parse an XML string stored in a database column or coming from another source into multiple columns and rows.

  • De-normalize nested XML into a flat structure
  • OutputAllUpstreamColumns keeps the other input columns
  • Pick the column, enter sample XML for metadata and preview

Parse XML tutorial

Filter & nested rows

Use a filter expression to extract nested data and turn one node into multiple rows.

  • Example: $.Customer.Orders[*]; regular expressions allowed in filters
  • IncludeParentColumns with optional ParentColumnPrefix
  • IncludeParentColumnsWhenChildMissing works like a LEFT OUTER JOIN

Component properties

Array handling & flattening

Control how repeating elements are treated and flatten arrays into columns.

  • ElementsToTreatAsArray forces elements to be treated as arrays
  • EnableArrayFlattening with MaxArrayItemsToFlatten
  • MaxLevelsToScan and ExcludedProperties

Component properties

Pivot & array transforms

Convert columns to rows, or reshape two-dimensional arrays and key/value data.

  • EnablePivot outputs Pivot_Name / Pivot_Value, plus optional Pivot_Path
  • ArrayTransformationType: 2-dimensional arrays, Key/Value to Columns and more

Component properties

Search & replace before parsing

Replace unwanted characters or custom escape sequences in the document text before it is parsed.

  • EnableCustomReplace with SearchFor / ReplaceWith
  • Add --regex or --regex-ic for regular expression search with group placeholders

Component properties

Empty & blank value handling

Decide what happens with empty input documents, blank values and missing properties.

  • EmptyInputProcessingMode: skip, output NULL, throw error or use a custom value
  • Treat blank numbers, booleans and dates as NULL
  • ThrowErrorIfPropertyMissing

Component properties

Data types, encoding & culture

Control how column types and lengths are detected and how the text is read.

  • MetaDataScanMode: Auto, Strict, Guess2x-10x, all string, MAX length or BLOB
  • Encoding / CharacterSet and Culture for number formats
  • MaxRows limits how many records are fetched

Component properties

SQL Server 2012–2025 & ADF

Runs in your environment on SQL Server 2012–2025 and Azure-SSIS.

  • SQL Server 2025, 2022, 2019, 2017, 2016, 2014, 2012 (32/64-bit)
  • Azure Data Factory (SSIS IR)
  • Design packages in Visual Studio 2017, 2019, 2022, or 2026
  • Windows only (64-bit OS): Windows 7–11 or Windows Server 2012–2025

Compatibility and requirements

Raw output & XML to JSON

Pass whole documents downstream as raw text instead of parsing them into columns.

  • OutputRawDocument with ConvertFormat to convert raw XML to JSON
  • Parse command line standard output (e.g. cmd:>curl -k http://httpbin.org/get)
  • EnablePerformanceMode for memory optimized parsing

Component properties

See all features
  • Parse source XML String/Documents into multiple columns/rows
  • Sample XML document defines the output columns
  • Ability to de-normalize nested XML data into flat structure
  • Support for expression to extract nested data and convert single node into multiple rows (e.g. $.Customer.Orders[*])
  • Option to parse output from command line Standard Output Stream (e.g. cmd:>curl -k http://httpbin.org/get)
  • Allow Regular Expression in Filter
  • Include parent columns, with LEFT OUTER JOIN style output when children are missing
  • Deep array flattening and ElementsToTreatAsArray
  • Pivot columns to rows (Pivot_Name / Pivot_Value) and 2-dimensional array transformations
  • Custom search / replace (including regex) before parsing
  • Handle empty input and treat blank number, bool or date values as NULL
  • Metadata scan modes, encoding, character set and culture options
  • Output raw XML documents, optionally converted to JSON
  • Include all upstream columns in the output
  • Memory optimized performance mode
  • Support for SQL Server 2025, 2022, 2019, 2017, 2016, 2014, 2012 (32/64-bit) and Azure Data Factory

Runs in your environment ·

SQL Server 2012–2025

and Azure-SSIS · Read the FAQ

How it works

From the toolbox to your first run.

Key steps from the XML Parser Transform page and help file, with the screens you will see in SSDT.

Turn an XML column into rows you can load anywhere.

Connect any upstream source, such as an OLE DB Source reading a table, to the XML Parser Transform. One input row can produce many output rows, which you can send to a CSV file or a database table such as SQL Server or MySQL.

  • De-normalize nested XML into a flat structure
  • OutputAllUpstreamColumns keeps the other input columns
  • Settings, Columns, General and Error Handling pages in the editor
XML Parser Transform - Parse XML Document, Convert XML to CSV or save to RDBMS (e.g. SQL Server, MySQL)

Inside the UI

Key settings, as they appear in SSDT.

Real screens from the XML Parser Transform product page. Step through them, or open any shot at full size.

Parse XML and load rows

100 XML documents from the source become 300 rows after parsing with filter $.Root.Branches[*], then go to a CSV file or a SQL Server table.

Compatibility and requirements

Designed to stay inside your SSIS runtime.

  • SQL Server 2012 through 2025, 32-bit and 64-bit
  • Windows only, 64-bit OS required. SQL Server Express and Web editions do not include SSIS
  • Azure Data Factory SSIS Integration Runtime
  • Self-hosted: credentials and data stay in your environment (external API calls still reach that service)
  • Fully functional 30-day trial of SSIS PowerPack

FAQ

Questions teams ask before they drop XML Parser Transform on the canvas.

What is XML Parser Transform?

SSIS XML Parser Transform parses an XML string into multiple columns and rows. It is useful when raw XML is stored in a database column or arrives from another source, such as an API response, and you need it as relational data.

What do I need to configure?

Select the input column that holds the XML, paste a sample XML document (used to determine the output columns and types), and optionally set a filter such as $.Customer.Orders[*]. Click Preview to check the rows. See the step-by-step tutorial.

How do I get one row per child node?

Use a filter that ends with [*] on the repeating node, e.g. $.Customers[*].Orders[*] for all orders of all customers. Turn on IncludeParentColumns to add the parent’s fields; IncludeParentColumnsWhenChildMissing also keeps parents without children, like a LEFT OUTER JOIN (the help notes this option is resource intensive).

Can I keep the other columns from the input row?

Yes. By default only the parsed columns are output. Check OutputAllUpstreamColumns to pass every other upstream column through as well.

Can it parse XML responses from an API call?

Yes. Connect the response output of Web API Destination to the XML Parser Transform, as shown in the Workday SOAP API and Zoho CRM tutorials.

Which SQL Server versions can I run it on?

SQL Server 2025, 2022, 2019, 2017, 2016, 2014, and 2012 (32- and 64-bit), plus Azure Data Factory with an SSIS Integration Runtime (packages must target SQL Server 2017). See Compatibility and requirements.

Independent ratings

Trusted by data teams, recognized by the industry

See why teams trust ZappySys (opens in new tab)

What customers say

  • Customer story

    ZappySys has been a fantastic way to bring our small company into the world of APIs, automation, and systems integration by providing tools within an easy-to-use framework.

    Ben KingeryOxspring Paul
  • Customer story

    At Synchronous Customers, we need to connect various types of data quickly. The super handy ZappySys connectors and great documentation let us do it without writing a lot of code.

    Joel ConnerSynchronous Customers
  • Customer story

    I looked everywhere for a product that could integrate to a web portal backend API via JSON. After many attempts, ZappySys was the only solution that gave me all the options I needed in a quick & clean interface within SSIS.

    Maz DunicSave the Children Australia
  • Customer story

    We have worked with the ZappySys support team for over a year and have consistently found them extremely helpful — resourceful and creative in offering multiple solutions to our challenges.

    Senthil SekarPublicis Groupe
  • Trustpilot

    I’ve been using ZappySys for over 6 years — it makes development faster, and I introduced it to my current company.

    Anna JohnsonTrustpilot review
  • Trustpilot

    I’m integrating data from APIs into database tables with SSIS/ZappySys — very easy to use, fast, and highly efficient without writing complex code.

    Carolyn S.Trustpilot review
  • Customer quote

    I submitted a support request and had someone logged in and fixing things within 2 hours.

    Barry Wilson
  • Customer quote

    The REST source is very powerful — the only thing I found I could use after trying components from major vendors.

    Jes KirkupKTC
  • Customer quote

    Whenever we run into an obstacle, ZappySys has a solution for us. We are so glad that we chose ZappySys PowerPack.

    Kevin ChenBerricle

Start your free trial today

Install SSIS PowerPack and drag XML Parser Transform onto the Data Flow.

Turn XML strings from any upstream column into rows and columns.

  • Data Flow Transform in SSISXML Parser Transform appears in the toolbox after you install PowerPack.
  • Fully functional 30-day trialSSIS PowerPack, running in your environment.
  • Live supportHelp during your trial and after purchase.

New purchases may be eligible for refund review within 30 days. See the refund policy.

Talk to an engineer before or during your trial.

1 / 4