Microsoft SQL Server Integration Services

Enhancing Data Integration with XML Transformations

MSDN Documentation > SQL Server Integration Services > Examples > XML Transformation

XML Transformation in SSIS

This section provides practical examples and guidance on leveraging the XML Transformation component within SQL Server Integration Services (SSIS) packages. The XML Transformation is a powerful tool for manipulating XML data, allowing you to format, filter, and restructure XML documents to meet specific business requirements.

Key Features and Benefits

Example 1: Basic XML Restructuring

Scenario

Imagine you have an incoming XML document representing customer orders, and you need to transform it to a format suitable for a reporting service, focusing only on the order ID and customer name.

Source XML Structure

<Orders>
    <Order id="1001">
        <Customer name="Alice Smith">
            <Item sku="A123" quantity="2"/>
            <Item sku="B456" quantity="1"/>
        </Customer>
        <Date>2023-10-27</Date>
    </Order>
    <Order id="1002">
        <Customer name="Bob Johnson">
            <Item sku="C789" quantity="5"/>
        </Customer>
        <Date>2023-10-28</Date>
    </Order>
</Orders>

Transformation Goal

Transform the source XML into a new structure containing only the order ID and customer name, like this: 

<SimplifiedOrders>
    <OrderSimplified orderId="1001" customerName="Alice Smith"/>
    <OrderSimplified orderId="1002" customerName="Bob Johnson"/>
</SimplifiedOrders>

SSIS Implementation Steps

  1. Add a Data Flow Task to your SSIS Control Flow.
  2. Inside the Data Flow Task, add an XML Source component and configure it to read your source XML.
  3. Add an XML Transformation component after the XML Source.
  4. In the XML Transformation Editor:
    • Map the @id attribute from the Order element to a new attribute named orderId in a target element OrderSimplified.
    • Map the @name attribute from the Customer element to a new attribute named customerName in the same OrderSimplified element.
    • Define the output XML structure as shown in the "Transformation Goal".
  5. Connect the output of the XML Transformation to an XML Destination or another appropriate component.

Example 2: Applying XSLT for Formatting

Scenario

You have XML data that needs to be presented in a human-readable HTML format for a web application. You can achieve this by applying an XSLT stylesheet.

Source XML Structure

<Products>
    <Product id="P100">
        <Name>Laptop</Name>
        <Price currency="USD">1200.50</Price>
    </Product>
    <Product id="P101">
        <Name>Keyboard</Name>
        <Price currency="USD">75.00</Price>
    </Product>
</Products>

XSLT Stylesheet (e.g., product_list.xslt)

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" indent="yes"/>

<xsl:template match="/Products">
    <html>
    <head>
        <title>Product List</title>
        <style>
            body { font-family: sans-serif; }
            table { border-collapse: collapse; width: 80%; margin: 20px auto; }
            th, td { border: 1px solid #ccc; padding: 10px; text-align: left; }
            th { background-color: #f2f2f2; }
        </style>
    </head>
    <body>
        <h1>Product Catalog</h1>
        <table>
            <tr>
                <th>Product ID</th>
                <th>Name</th>
                <th>Price</th>
            </tr>
            <xsl:apply-templates select="Product"/>
        </table>
    </body>
    </html>
</xsl:template>

<xsl:template match="Product">
    <tr>
        <td><xsl:value-of select="@id"/></td>
        <td><xsl:value-of select="Name"/></td>
        <td><xsl:value-of select="Price"/> <xsl:value-of select="Price/@currency"/></td>
    </tr>
</xsl:template>

</xsl:stylesheet>

SSIS Implementation Steps

  1. Add a Data Flow Task.
  2. Use an XML Source component to read the source XML.
  3. Add an XML Transformation component.
  4. In the XML Transformation Editor, select the "XSLT Transformation" tab.
  5. Browse to and select your XSLT file (product_list.xslt).
  6. The output will be the HTML document generated by the XSLT.
  7. Route this HTML output to a File Destination component to save it as an HTML file.

Further Resources