How to generate XML with namespace / prefix in SSIS

Introduction

In this post you will see how to generate XML which has namespace and prefix for elements. We will use SSIS XML Generator Transform for this purpose. Common pattern would be generate XML SOAP message using XML Generator Transform and send to Web service using SSIS Web API Destination. For full tutorial on how to export XML using SSIS check this link.

Understanding XML Namespace and Prefix

Just like how many programming languages offer concept called namespace to avoid similar name conflict for classes, XML also has namespace concept.

Sample XML with namespace and prefix

If you notice in above XML there are two important things. Under <soapenv:Envelope> element you have xmlns:p1 and xmlns:soapenv attributes. Basically these are two namespace declarations and they both use different prefix (i.e. p1 and soapenv). Once you define namespace prefix you can use in the declaring element or any child element under that node.

Syntax for XML Namespace declaration:

Example – Using SSIS XML Generator Transform

Now lets look at how to generate XML using SSIS XML Generator Transform. This same technique can be used by SSIS Export XML Task.

1. Download and Install SSIS PowerPack from here (Skip this if you already did)
2. Open Visual Studio and create new SSIS Package
3. Drag Data Flow Task from Control Flow Toolbox
   

   Dragging and dropping Data Flow Task into Control Flow

4. Drag ZS Dummy Data Source (You can use any source you like. e.g. OLEDB Source)
5. Change Template to Customer to create fake customer data. Click OK to save.
6. Now drag and drop ZS XML Generator Transform
7. Connect Source to XML Generator
8. Double click XML Generator to configure.
9. Go to Options tab and change following setting
   1. under Namespaces define two namespaces as below (Change p1 to desired prefix)
      p1=http://someurl, soapenv=http://schemas.xmlsoap.org/soap/envelope/
   2. Change Root element name to Envelope
   3. Change Root Element Prefix to soapenv
      

      Configure SSIS XML Generator Transform – Define namespace and prefix , root element

10. Now go to layout tab and select dataset and click Edit
11. Change default Row element name to Body and prefix to soapenv as below. Click OK to save
    

    Configure SSIS XML Generator Transform – Change Row element name and prefix

12. Now right click on Mappings folder and Click “Add Elements” or click in the toolbar icon.
13. On the Add Elements dialog
    1. select Add multiple option.
    2. Change Prefix to p1 (or whatever you entered before on Options tab)
    3. Select columns you want to output in XML
       

       Configure SSIS XML Generator Transform – Add elements with prefix

14. Now you will see preview update for your XML layout as below
    

    SSIS XML Generator Transform – Preview of complex XML using namespace and prefix

15. Now attach your XML Generator Transform to some downstream component such as ZS Trash Destination and run the package

Conclusion

Using SSIS PowerPack you can generate complex XML in few clicks. Download SSIS PowerPack for FREE Trial and discover many more features.