How to parse XML stored inside a SQL Server column using SSIS

Introduction

SQL Server can store XML data inside a column using the XML data type. In this article, we will show how to parse the XML data using the SSIS Xml Parser Transform 

Getting Started

1. Create a SQL Server table with XML data in a column:USE [Northwind]
   
   GO
   
   /****** Object: Table [dbo].[xmltbl] Script Date: 18/10/2019 18:24:13 ******/
   
   SET ANSI_NULLS ON
   
   GO
   
   SET QUOTED_IDENTIFIER ON
   
   GO
   
   CREATE TABLE [dbo].[xmltbl](
   
    [c1] [smallint] NULL,
   
    [data] [xml] NULL
   
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   
   GO
   
   INSERT [dbo].[xmltbl] ([c1], [data]) VALUES (1, N'<catalog><book id="bk101"><author>Gambardella, Matthew</author><title>XML Developers Guide</title><genre>Computer</genre><price>44.95</price><publish_date>2000-10-01</publish_date><description>An in-depth look at creating applications 
   
    with XML.</description></book><book id="bk102"><author>Ralls, Kim</author><title>Midnight Rain</title><genre>Fantasy</genre><price>5.95</price><publish_date>2000-12-16</publish_date><description>A former architect battles corporate zombies, 
   
    an evil sorceress, and her own childhood to become queen 
   
    of the world.</description></book><book id="bk103"><author>Corets, Eva</author><title>Maeve Ascendant</title><genre>Fantasy</genre><price>5.95</price><publish_date>2000-11-17</publish_date><description>After the collapse of a nanotechnology 
   
    society in England, the young survivors lay the 
   
    foundation for a new society.</description></book><book id="bk104"><author>Corets, Eva</author><title>Oberon''s Legacy</title><genre>Fantasy</genre><price>5.95</price><publish_date>2001-03-10</publish_date><description>In post-apocalypse England, the mysterious 
   
    agent known only as Oberon helps to create a new life 
   
    for the inhabitants of London. Sequel to Maeve 
   
    Ascendant.</description></book><book id="bk105"><author>Corets, Eva</author><title>The Sundered Grail</title><genre>Fantasy</genre><price>5.95</price><publish_date>2001-09-10</publish_date><description>The two daughters of Maeve, half-sisters, 
   
    battle one another for control of England. Sequel to 
   
    Oberons Legacy.</description></book><book id="bk106"><author>Randall, Cynthia</author><title>Lover Birds</title><genre>Romance</genre><price>4.95</price><publish_date>2000-09-02</publish_date><description>When Carla meets Paul at an ornithology 
   
    conference, tempers fly as feathers get ruffled.</description></book><book id="bk107"><author>Thurman, Paula</author><title>Splish Splash</title><genre>Romance</genre><price>4.95</price><publish_date>2000-11-02</publish_date><description>A deep sea diver finds true love twenty 
   
    thousand leagues beneath the sea.</description></book><book id="bk108"><author>Knorr, Stefan</author><title>Creepy Crawlies</title><genre>Horror</genre><price>4.95</price><publish_date>2000-12-06</publish_date><description>An anthology of horror stories about roaches,
   
    centipedes, scorpions and other insects.</description></book><book id="bk109"><author>Kress, Peter</author><title>Paradox Lost</title><genre>Science Fiction</genre><price>6.95</price><publish_date>2000-11-02</publish_date><description>After an inadvertant trip through a Heisenberg
   
    Uncertainty Device, James Salway discovers the problems 
   
    of being quantum.</description></book><book id="bk110"><author>OBrien, Tim</author><title>Microsoft .NET: The Programming Bible</title><genre>Computer</genre><price>36.95</price><publish_date>2000-12-09</publish_date><description>Microsoft.NET initiative is explored in 
   
    detail in this deep programmer reference.</description></book><book id="bk111"><author>OBrien, Tim</author><title>MSXML3: A Comprehensive Guide</title><genre>Computer</genre><price>36.95</price><publish_date>2000-12-01</publish_date><description>The Microsoft MSXML3 parser is covered in 
   
    detail, with attention to XML DOM interfaces, XSLT processing, 
   
    SAX and more.</description></book><book id="bk112"><author>Galos, Mike</author><title>Visual Studio 7: A Comprehensive Guide</title><genre>Computer</genre><price>49.95</price><publish_date>2001-04-16</publish_date><description>Microsoft Visual Studio 7 is explored in depth,
   
    looking at how Visual Basic, Visual C++, C#, and ASP+ are 
   
    integrated into a comprehensive development 
   
    environment.</description></book></catalog>')

2. Create an SSIS Project. Drag and drop the Data Flow task and double click it. 
3. In the Data Flow tab, drag and drop the following components and join them:
4. Double click the OLE DB Source and connect to the table with the XML data type:
5. Using the XML Parser Transform, select the input and write the XML String that you want to parse:
6. In this example, we will use the following Array Filter:

   $.catalog.book[*].author

7. Once parsed you can create a destination table using the Oledb destination task:

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to [e-mail]

https://zappysys.com/support/