SQLServerCentral Article

Basics of XML and SQL Server, Part 4: Create an XML invoice with SSIS

,

One of the more common tasks for which XML is used for is to automate invoicing. This is what a simple text invoice looks like.

This is what an analogous XML invoice looks like.

Creating an XML document like an invoice is more art than science. There is no single correct way to do it. Having a paper document to use as a model helps a lot. You can decompose the model document into its individual sections and build their XML analogs one section at a time, then stitch them all together into a single unit.

Demo Data

Execute the following SQL script in SQL Server Management Studio to create the demo tables containing the data used to generate the preceding invoices.

USE master
GO
BEGIN TRY
DROP TABLE [InvoiceDemo].[dbo].[Vendor]
END TRY
BEGIN CATCH
END CATCH
GO
BEGIN TRY
DROP TABLE [InvoiceDemo].[dbo].[Clients]
END TRY
BEGIN CATCH
END CATCH
GO
BEGIN TRY
DROP TABLE [InvoiceDemo].[dbo].[Sales]
END TRY
BEGIN CATCH
END CATCH
GO
BEGIN TRY
DROP DATABASE InvoiceDemo
END TRY
BEGIN CATCH
END CATCH
GO
CREATE DATABASE InvoiceDemo
GO
USE InvoiceDemo
GO
CREATE TABLE [InvoiceDemo].[dbo].[Vendor]
(
    [VendorName] VARCHAR(50),
    [Address] VARCHAR(50),
    [City] VARCHAR(50),
    [State] VARCHAR(2),
    [ZipCode] VARCHAR(10),
    [Phone] VARCHAR(12)
)
GO
INSERT INTO [InvoiceDemo].[dbo].[Vendor]
([VendorName],[Address],[City],[State],[ZipCode],[Phone])
VALUES
('Gasoline and Diesel Suppliers, LLC','1234 Petroleum Blvd','Oil Town','XX','9999-9999','999-999-9999')
GO
CREATE TABLE [InvoiceDemo].[dbo].[Clients]
(
    [ClientID] VARCHAR(50),
    [ClientName] VARCHAR(50),
    [Address] VARCHAR(50),
    [City] VARCHAR(50),
    [State] VARCHAR(50),
    [ZipCode] VARCHAR(50),
    [Phone] VARCHAR(50)
)
GO
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('1','Jiffee Stop Gas','1234 Monroe Street','Washington','XX','9999-9999','999-999-9999')
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('2','Fast Gas and Sundries','5678 Jefferson Boulevard','Adams','XX','9999-9999','999-999-9999')
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('3','I-90 Truck Stop','9876 Lincoln Avenue','Jackson','XX','9999-9999','999-999-9999')
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('4','Gas and Go','1029 Truman Road','Hamilton','XX','9999-9999','999-999-9999')
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('5','Racetrack Gas','3847 US Grant Freeway','Franklin','XX','9999-9999','999-999-9999')
GO
CREATE TABLE [InvoiceDemo].[dbo].[Sales]
(
    [ClientID] VARCHAR(50),
    [InvoiceNumber] VARCHAR(50),
    [InvoiceDate] DATETIME,
    [ItemID] VARCHAR(50),
    [ItemDescription] VARCHAR(50),
    [Gallons] VARCHAR(50),
    [PricePerGallon] VARCHAR(50),
    [Amount] AS (-1) * CAST(CAST([Gallons] AS FLOAT) * CAST([PricePerGallon] AS FLOAT) AS MONEY)
)
GO
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('1','1001','01/01/2012','1','Regular Unleaded','5000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('1','1001','01/01/2012','2','Premium Unleaded','3000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('1','1001','01/01/2012','3','No. 2 Diesel','2000','4.50')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('2','1002','01/12/2012','1','Regular Unleaded','2000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('2','1002','01/12/2012','2','Premium Unleaded','1000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('2','1002','01/12/2012','3','No. 2 Diesel','500','4.50')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('3','1003','01/09/2012','1','Regular Unleaded','10000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('3','1003','01/09/2012','2','Premium Unleaded','5000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('3','1003','01/09/2012','3','No. 2 Diesel','2500','4.50')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('4','1004','01/22/2012','1','Regular Unleaded','6000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('4','1004','01/22/2012','2','Premium Unleaded','6000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('4','1004','01/22/2012','3','No. 2 Diesel','3000','4.50')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('5','1005','01/17/2012','1','Regular Unleaded','15000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('5','1005','01/17/2012','2','Premium Unleaded','7000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('5','1005','01/17/2012','3','No. 2 Diesel','5500','4.50')
GO
SELECT * FROM [InvoiceDemo].[dbo].[Vendor]
GO
SELECT * FROM [InvoiceDemo].[dbo].[Clients]
GO
SELECT * FROM [InvoiceDemo].[dbo].[Sales]

The SQL script creates three tables; Vendor, Client and Sales.

Generate the XML...

The following SQL script was used to create the previous XML invoice for Client #1, Jiffee Stop Gas, from the above data.

DECLARE @ClientID VARCHAR(10)
SET @ClientID = 1
DECLARE @xml AS NVARCHAR(MAX)
DECLARE @InvoiceInfo NVARCHAR(MAX)
DECLARE @Vendor NVARCHAR(MAX)
DECLARE @Client NVARCHAR(MAX)
DECLARE @Items NVARCHAR(MAX)
DECLARE @BalanceDue NVARCHAR(MAX)
SET @InvoiceInfo = (
SELECT DISTINCT
    InvoiceNumber,
    CONVERT(VARCHAR(10),InvoiceDate,101) AS InvoiceDate
FROM [InvoiceDemo].[dbo].[Sales]
WHERE ClientID = @ClientID
FOR XML PATH('InvoiceInfo')
)
SET @Vendor =
(
SELECT VendorName,Address,City,[State],ZipCode,Phone
FROM [InvoiceDemo].[dbo].[Vendor]
FOR XML PATH('Vendor')
)
SET @Client =
(
SELECT ClientId,ClientName,Address,City,[State],ZipCode,Phone
FROM [InvoiceDemo].[dbo].[Clients]
WHERE ClientId = 1
FOR XML PATH('Client')
)
SET @Items = (
SELECT
    ItemID,
    ItemDescription,
    Gallons,
    PricePerGallon,
    Amount
FROM [InvoiceDemo].[dbo].[Sales]
WHERE ClientID = @ClientID
FOR XML PATH('Item')
)
SET @BalanceDue = (
SELECT
    SUM(Amount)
FROM [InvoiceDemo].[dbo].[Sales]
WHERE ClientID = @ClientID
FOR XML PATH('BalanceDue')
)
SET @xml = '<Invoice>' + @InvoiceInfo + @Vendor + @Client + '<Charges>' + @Items + '</Charges>' + @BalanceDue + '</Invoice>'
SELECT @xml AS text
SELECT CAST(@xml AS XML) AS xml

Execute the script in SQL Server Management Studio to produce text and XML results.

The two SELECT statements at the end of the script allow the XML code to be displayed as a VARCHAR(MAX) text datatype, and as an XML data type.  The text datatype is what will be captured to a file by the SSIS package. You can click on the link of the XML data type output to genererate the XML document in a new tab of Management Studio.

...One Section at a Time...

The first section of the XML invoice (Invoice Number and Invoice Date) was stored in the VARCHAR(MAX) variable '@InvoiceInfo' by the following portion of the SQL script.

The second section of the XML invoice, Vendor, was stored in the '@Vendor' VARCHAR(MAX) variable by this portion of the SQL script.

The third section of the XML invoice, Client, was stored in the '@Cliient' VARCHAR(MAX) variable by this portion of the SQL script.

The fourth section of the XML invoice, Charges, was stored in the '@Items' VARCHAR(MAX) variable by this portion of the SQL script.

The last section of the XML invoice, Balance Due, was stored in the '@BalanceDue' VARCHAR(MAX) variable by this portion of the SQL script.

...Then Stitch the Sections Together

Finally, the five VARCHAR(MAX) variables we created above are stitched together to create the contents of the XML invoice document.

Now that we have the SQL script needed to generate the invoice, we are ready to create an SSIS package that executes the script and saves the output of the to a file.

Create the Demo SSIS Package

Create an SSIS package in Business Intelligence Development Studio with an Execute SQL Task, a Script Task, a Precedence Constraint connecting the two tasks, and package variables named client_id, output_path_template and xml_doc as shown in the screen shot below.

Double-click the Get Client ID script task, choose Microsoft Visual Basic 2008 from the ScriptLanguage menu list, enter User::client_id in the ReadWriteVariables text box, then click the Edit Script button

        Dim data As String = InputBox("Please enter a client ID from 1 to 5.", "Data Input")
        Dts.Variables("client_id").Value = data

Right-click the Connection Managers panel and select New OLE DB Connection...

Click the New button in the Configure OLE DB Connection Manager dialog box.

Enter the name of the server and database where the demo data was created and click the OK button.

Confirm that the data connection has been created and click the OK button.

Change the name of the connection manager to something meaningful if you wish, then double-click the Create XML Invoice content SQL Task.

Click on the SQL Statement line to bring up the button. Click on the button..

...to bring up the Enter SQL Query box...

...and paste this SQL code into it.

DECLARE @ClientID VARCHAR(10)
SET @ClientID = 1
DECLARE @xml AS NVARCHAR(MAX)
DECLARE @InvoiceInfo NVARCHAR(MAX)
DECLARE @Vendor NVARCHAR(MAX)
DECLARE @Client NVARCHAR(MAX)
DECLARE @Items NVARCHAR(MAX)
DECLARE @BalanceDue NVARCHAR(MAX)
SET @InvoiceInfo = (
SELECT DISTINCT
    InvoiceNumber,
    CONVERT(VARCHAR(10),InvoiceDate,101) AS InvoiceDate
FROM [InvoiceDemo].[dbo].[Sales]
WHERE ClientID = @ClientID
FOR XML PATH('InvoiceInfo')
)
SET @Vendor =
(
SELECT VendorName,Address,City,[State],ZipCode,Phone
FROM [InvoiceDemo].[dbo].[Vendor]
FOR XML PATH('Vendor')
)
SET @Client =
(
SELECT ClientId,ClientName,Address,City,[State],ZipCode,Phone
FROM [InvoiceDemo].[dbo].[Clients]
WHERE ClientId = 1
FOR XML PATH('Client')
)
SET @Items = (
SELECT
    ItemID,
    ItemDescription,
    Gallons,
    PricePerGallon,
    Amount
FROM [InvoiceDemo].[dbo].[Sales]
WHERE ClientID = @ClientID
FOR XML PATH('Item')
)
SET @BalanceDue = (
SELECT
    SUM(Amount)
FROM [InvoiceDemo].[dbo].[Sales]
WHERE ClientID = @ClientID
FOR XML PATH('BalanceDue')
)
SET @xml = '<Invoice>' + @InvoiceInfo + @Vendor + @Client + '<Charges>' + @Items + '</Charges>' + @BalanceDue + '</Invoice>'
SELECT @xml AS text

Click the OK button of the Enter SQL Query text box.

Click the OK button of the Execute SQL Task Editor.

Double-click the Write XML invoice to file script task component.

Click on the ReadOnlyVariables line, then on the button to bring up the Select Variables dialog box, select the User::xml_doc check box, then click the Edit Script button.

Enter the follwing code...

        Dim client_id As String = Dts.Variables("client_id").Value.ToString
        Dim output_path_template As String = Dts.Variables("output_path_template").Value.ToString
        Dim output_path As String = output_path_template.Replace("*", client_id)
        Dim doc As New System.Xml.XmlDocument
        doc.InnerXml = Replace(Replace(Dts.Variables("xml_doc").Value.ToString, "<ROOT>", ""), "</ROOT>", "")
        doc.InnerXml = "<?xml version=""1.0"" encoding=""ISO-8859-1""?><?xml-stylesheet type=""text/xsl"" href=""Invoice.xsl""?>" + doc.InnerXml
        doc.Save(output_path)

...into the script task editor, then save and close it, and click the OK button in the Script Task Editor configuration button.

The VB.NET code above creates the output file name from the SSIS package variables client_id and output_path, embedding the client ID in the path name. It then strips out the <ROOT> and </ROOT> element tags that were inserted by the System.Xml.XmlDocument class, followed by adding the encoding and stylesheet reference tags to the XML document so it can be rendered in a browser when invoked.

Execute the Demo SSIS Package

Create a directory named C:\xml4\ and copy the attached Invoice.xsl file to it.

Execute the SSIS package and enter the desired Client ID in the dialog box...

...to create an XML file..

...in the C:\xml4\ directory.

Double-click on the Invoice.xml file to transform and display it in your default browser.

Following is the XSLT stylsheet used to display the XML invoice.

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
  <html>
  <body>
  <h2>INVOICE</h2>
    <xsl:for-each select="Invoice/InvoiceInfo">
    <table border="1">
      <tr><td align="right" bgcolor="#AAAAAA"><b>Invoice Number:</b></td><td><xsl:value-of select="InvoiceNumber"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>Invoice Date:</b></td><td><xsl:value-of select="InvoiceDate"/></td></tr>
    </table>
    </xsl:for-each>
  <h2>VENDOR</h2>
    <xsl:for-each select="Invoice/Vendor">
    <table border="1">
      <tr><td align="right" bgcolor="#AAAAAA"><b>Vendor Name:</b></td><td><xsl:value-of select="VendorName"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>Address:</b></td><td><xsl:value-of select="Address"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>City:</b></td><td><xsl:value-of select="City"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>State:</b></td><td><xsl:value-of select="State"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>Zip Code:</b></td><td><xsl:value-of select="ZipCode"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>Phone:</b></td><td><xsl:value-of select="Phone"/></td></tr>
    </table>
    </xsl:for-each>
  <h2>CLIENT</h2>
    <xsl:for-each select="Invoice/Client">
    <table border="1">
      <tr><td align="right" bgcolor="#AAAAAA"><b>Client Name:</b></td><td><xsl:value-of select="ClientName"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>Address:</b></td><td><xsl:value-of select="Address"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>City:</b></td><td><xsl:value-of select="City"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>State:</b></td><td><xsl:value-of select="State"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>Zip Code:</b></td><td><xsl:value-of select="ZipCode"/></td></tr>
      <tr><td align="right" bgcolor="#AAAAAA"><b>Phone:</b></td><td><xsl:value-of select="Phone"/></td></tr>
    </table>
    </xsl:for-each>
  <h2>CHARGES</h2>
  <table border="1">
    <tr bgcolor="#AAAAAA">
      <th align="center">Item ID</th>
      <th align="left">Item Description</th>
      <th align="right">Gallons</th>
      <th align="right">Price per Gallon</th>
      <th align="right">Amount</th>
    </tr>
    <xsl:for-each select="Invoice/Charges/Item">
    <tr>
      <td align="center"><xsl:value-of select="ItemID"/></td>
      <td align="left"><xsl:value-of select="ItemDescription"/></td>
      <td align="right"><xsl:value-of select="Gallons"/></td>
      <td align="right"><xsl:value-of select="PricePerGallon"/></td>
      <td align="right"><xsl:value-of select="Amount"/></td>
    </tr>
    </xsl:for-each>
  </table>
  <h2>BALANCE DUE</h2>
    <xsl:for-each select="Invoice">
    <table border="1">
      <tr><td align="right" bgcolor="#AAAAAA"><b>Balance Due:</b></td><td><xsl:value-of select="BalanceDue"/></td></tr>
    </table>
    </xsl:for-each>
  </body>
  </html>
</xsl:template>
</xsl:stylesheet>

Try deleting sections from the stylesheet and observing the changes in the displayed document, then try recreating the deleted sections by cloning and modifiying one of the remaining sections.

Disclaimer

This SSIS package is a prototype, intended for demonstration purposes only.

Resources

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.36 (11)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.36 (11)

You rated this post out of 5. Change rating

Related content

External Article

Export to XML Using SSIS

Exporting data to XML format using SSIS initially seems like it should be straight forward – just dump it in a flat file and give it a name xml type, however SSIS has no XML destination just an XML source. I had no luck dumping the XML in flat file. My solution was to use a script task which worked well.

2013-06-27

3,831 reads