Part 2: Mapping XML from SQL Server to a Single Cell in Excel 2003
Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services
Testing: Get Your Customers Involved in the Testing Process with Functional Tests in Excel
Collapse the table of content
Expand the table of content

Part 2: Mapping XML from SQL Server to a Single Cell in Excel 2003

Office 2003

Sergei Gundorov
Clark Hamilton
Microsoft Corporation

March 2005

Applies to:
    Microsoft Office Editions 2003

Summary: In the second of this three-part series, learn how to map well-formed XML from a standard SQL Server query to a single cell in a worksheet. The resulting output is an XML tree with non-repeating elements. (20 printed pages)


Underlying Problems That the Sample Code Addresses
SQL XML Output Requirements
Automating the Transformation Process
Exploring the XML Map
Manually Importing and Exporting XML Data
Additional Resources


XML maps in Microsoft Office Excel 2003 provide one of the fastest ways to import data into a spreadsheet. The methodology works seamlessly for data files with repeating elements that take advantage of a list object. However, many scenarios call for a single-mapped cell or one-to-one XML element to cell mapping. Most XML sources don't provide the data in the non-repeating element, XML tree format that is necessary for single-cell mapping. Creating XSLT templates to transform a typical FOR XML RAW input into an XML tree can be complex and requires in-depth, specialized knowledge.

This article presents a straightforward and generic programmatic solution for consuming well-formed XML from a standard Microsoft SQL Server query output of SELECT ...FOR XML RAW (for example, <ROOT><row attr1="Value1-1" attr2="Value1-2".../><row attr1="Value2-1" attr2="Value2-2".../>...</ROOT>). The output is an XML tree with non-repeating elements, which is required for Excel single-mapped cells.

Underlying Problems That the Sample Code Addresses

Single-cell mapping in Excel has specific requirements:

  • Elements must be non-repeating, or Excel mapping will immediately create a list.
  • Data must be presented in an XML tree format when dealing with more than one dimension.

The data source side presents its own challenges:

  • Data rarely comes in an XML tree format compatible with Excel single-cell mapping.
  • Depending on the nature of the data, it can have a varying number of dimensions; also, dimension names change from data source to data source.

The latter is one of the main reasons why we decided to create an XML conversion component instead of using XSLT. We needed to transform varying data inputs, with different numbers of dimensions and dimension element names. We ruled out an XSLT plus FOR XML EXPLICIT approach because it did not provide the desired level of flexibility. The following code gives us the ability to use any single data value query result with the FOR XML RAW clause when we have non-repeating dimension value combinations.

Here are a couple of sample files with source input from the SQL XML SELECT ... FOR XML RAW query and its transformed output:

1. Four Dimensions


<?xml version="1.0" encoding="utf-8" ?><root>
    <row SlideName="Client" SectionName="Revenue" RowName="Academic" ColumnName="ACT_CUR" DataValue="1000" />
    <row SlideName="Client" SectionName="Revenue" RowName="Academic" ColumnName="ACT_YTD" DataValue="1000" />
    <row SlideName="Client" SectionName="Revenue" RowName="Commercial" ColumnName="ACT_CUR" DataValue="1000" />
    <row SlideName="Client" SectionName="Revenue" RowName="Commercial" ColumnName="ACT_YTD" DataValue="1000" />

Transformed Output Compatible with Excel Single-Cell Mapping:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<ROOT xmlns:xsi="">

2. Two Dimensions


<?xml version="1.0" encoding="utf-8" ?>
    <row SlideName="Client" CommentName="Comment1" DataValue="value" />
    <row SlideName="Client" CommentName="Comment2" DataValue="value2" />

Transformed Output Compatible with Excel Single-Cell Mapping:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<ROOT xmlns:xsi="">

SQL XML Output Requirements

The SQL XML FOR XML RAW query output presents an ideal XML format for programmatically converting source data from database tables into Excel single-mapped cells because it consistently supplies data in the standardized form (for example, the <row ../> element is guaranteed to be there). We can rely on the structure of the source XML document or XML file for our transformation steps. RAW XML is the first requirement of our sample code. The other requirements are:

  • All dimension elements must be non-repeating. You can achieve this by adding the GROUP BY [dimensions list] clause to the query.
  • Dimension attributes must be in hierarchical order from the highest dimension to the lowest. For example: SheetName (top-level dimension), TableName (next level down; the sheet may have one or more unique tables that can also appear on other sheets), RowName (next level down; the same RowName can appear once in other tables on the same or other sheets), ColumnName (next level down; the same ColumnName can appear once under the same RowName, but it can be present in other RowNames in the same table).
  • You must include a data value attribute immediately following the attributes that contain the dimension element names. The data value attribute holds the value that maps to an Excel cell.
  • Dimension attribute values cannot contain spaces and special characters because they will become XML elements. For example, <row SlideName="ServerTools" .../> is a valid attribute value because it can become an element name. However, <row SlideName="Server & Tools" .../> is not valid because it contains two spaces and an ampersand and therefore cannot become an element name. You should consult XML documentation for more details about element name restrictions.

The following sample query follows these requirements and produces the four-dimension sample XML shown earlier:

SELECT SlideName, SectionName,RowName,ColumnName,SUM(DataValue) as DataValue FROM [source table name]
WHERE [filter]
GROUP BY SlideName, SectionName,RowName,ColumnName

The GROUP BY clause that includes all dimensions guarantees that the final output file will contain non-repeating elements.

Note   The SQL Server query generates an XML fragment. Our code can consume this fragment without a header, or you can choose to add an XML header programmatically when retrieving the data to facilitate other manipulations.

Automating the Transformation Process

Our goal was to create a universal, reusable component to work with an XML Document object that holds an SQL XML FOR XML RAW query result that meets the requirements listed in the previous section. We reviewed many scenarios for importing data into an Excel workbook, and the most efficient scenario used one database call for each XML source with the same dimensionality (to minimize network traffic), and had one map in each sheet for each type of map (that is, a map with the same dimensions). It is important to note that, in our tests, one big XML map that included data for all sheets in the workbook performed slower than individual sheet-by-sheet maps (approximately 6,000 records per workbook with 15 worksheets). This is why we implemented the splitAttr variable in our code. We split the source file into multiple files based on this attribute. Typically you would want the splitAttr variable to be a sheet name or a reference to a sheet name. Excel XML maps will infer a schema from the XML tree non-repeating element source, so the only thing left to do is generate properly formatted XML input. The following sample code does just that.

Microsoft Visual Basic .NET Example

Option Explicit On 
Imports System.Xml
Imports System.Windows.Forms

Namespace CustomXMLTransform
    'Purpose: bypass complex XSLT transformations and have
    'generic object to read in any SQL XML .. FOR XML RAW input with 
    'variable number of dimensions and non-repeating elements 
    'compatible with Excel single-cell mapping format.
    'Output as an XMLDocument object and XML file(s)
    'ready for Excel XMLMap use   

    'NOTE: setting up special class seems to be as fast as a hashtable
    'and allows for easier code port to VB or VBA if .NET solution is 
    'not an option
    'use Type for VB or VBA
    'NOTE: reference MS XML V5 in VBA or VB project.
    'Type xDOMCollection
    '    Name As String
    '    xDOM As New MSXML2.DOMDocument
    'End Type
    Friend Class xDOMCollection
        Public Name As String
        Public xDOM As New XmlDocument
    End Class

    'Error handling deliberately left out, so that using classes can 
    'write own handling
    Public Class RawXMLToXMLTree

        'pre-allocating 50 slots for xDOMCollection entries
        Public Const FILE_COUNT As Integer = 50
        Friend Shared xDOMOut(FILE_COUNT) As xDOMCollection

        'NOTE: can add return type as XMLDocuments collection
        '(for example, ArrayList)

        Public Shared Function TransformXMLDom( _
                                ByVal xDOMIn As XmlDocument, _
                                ByVal outputPath As String, _
                                ByVal splitAttr As String, _
                                ByVal dataValueAttr As String)

            Dim rowNodeList As System.Xml.XmlNodeList
            Dim xmlKeyNode As System.Xml.XmlNode
            Dim curNode As String
            Dim prevNode As String
            Dim curElement As String
            Dim i As Integer
            Dim ii As Integer
            Dim xDOMIndex As Integer

            'Construction of the container
            'the upper boundary can be passed as a parameter
            'or xDOMCollection can be rewritten as Hashtable
            For i = 0 To FILE_COUNT
                RawXMLToXMLTree.xDOMOut(i) = New xDOMCollection

            'selecting all rows
            'since using FOR XML RAW <row../> element will be there by
            rowNodeList = xDOMIn.SelectNodes("//row")

            'processing every row in the source file
            'and adding new node to the output file if the node is not
            'found stop at DataValue attribute and add as value to the 
            'lowest node in the output file
            For i = 0 To rowNodeList.Count - 1
                'resetting curnode value
                'Every output file will have DocumentElement ROOT
                prevNode = "ROOT"
                For ii = 0 To rowNodeList(i).Attributes.Count - 1
                    'determining if xDOM for this split attribute value
                    'already exists and setting collection index to point
                    'to correct DOM in collection allows for any sort 
                    'order in the source raw XML and is faster than 
                    'resetting rowNodeList to 
                    If rowNodeList(i).Attributes.Item(ii).Name = _
                        splitAttr Then
                        xDOMIndex = RawXMLToXMLTree.GetxDomIndex _
                    End If
                    'constructing current node query string
                    If Not rowNodeList(i).Attributes(ii).Name = _
                        dataValueAttr Then
                        curElement = rowNodeList(i).Attributes(ii).Value
                        curNode = prevNode & "/" & rowNodeList(i). _
                        'faster to use set here as opposed to 
                        'selectSingleNode in the Else clause
                        xmlKeyNode = RawXMLToXMLTree.GetNode(xDOMIn, _
                        xDOMOut(xDOMIndex).xDOM, prevNode, curNode, _
                        'if successfully added a node then setting 
                        'prevNode to current node for next iteration
                        prevNode = curNode
                        'adding data value to XMLKey node in the XML 
                        'Tree output
                        xmlKeyNode.InnerText = rowNodeList(i). _
                        'stop processing record after data value attribute
                        'Covers case when SQL source XML file has 
                        'additional attributes for metadata
                        Exit For
                    End If

            'processed file is saved off to multiple files
            For i = 0 To FILE_COUNT
                'exiting collection if encounter first blank entry
                If xDOMOut(i).Name = "" Then Exit For
                'saving off XML file compatible with Excel single 
                'cell mapping to the disk
                'can be put in any container object for direct Excel XML
                'Map import method as well
                xDOMOut(i).xDOM.Save(outputPath & xDOMOut(i).Name & _
                'Here can add extension for loading XMLDocument 
                'to another object or 
                'Excel(XMLMap imorting)
                'Sample line: [Workbook_Object].XmlMaps _
                '("[Your_Map_Name]").ImportXml [XMLDocument Object].XML

        End Function

        Shared Function GetNode( _
                            ByRef xDOMIn As XmlDocument, _
                            ByVal xDOMOut As XmlDocument, _
                            ByVal prevNode As String, _
                            ByVal curNode As String, _
                            ByVal curElement As String) _
                            As System.Xml.XmlNode

            Dim activeNode As XmlNode
            Dim outCurrentNode As XmlNode
            Dim nodeOut As XmlNode

            activeNode = xDOMOut.SelectSingleNode(curNode)

            'this test provides maximum efficiency
            'we need to add dimension elements and data value(s) on 
            'single pass through xDOMIn
            If activeNode Is Nothing Then
                GetNode = xDOMOut.SelectSingleNode(prevNode)
                'in the output document always begin to add 
                'file split attribute (top level)
                'at the ROOT level
                nodeOut = xDOMOut.CreateNode(XmlNodeType.Element, _
                          curElement, "")
                GetNode = GetNode.AppendChild(nodeOut)
            End If
        End Function

        Shared Function GetxDomIndex(ByVal splitAttr As String) _
                            As Integer

            Dim i As Integer

            For i = 0 To FILE_COUNT
                If RawXMLToXMLTree.xDOMOut(i).Name = splitAttr Then
                    Exit For
                ElseIf RawXMLToXMLTree.xDOMOut(i).Name = "" Then
                    RawXMLToXMLTree.xDOMOut(i).Name = splitAttr
                    RawXMLToXMLTree.xDOMOut(i).xDOM.LoadXml( _
                    "<?xml version=""1.0"" encoding=""UTF-8""" & _
                    " standalone=""yes""?>" & _
                    "<ROOT xmlns:xsi=""" & _
                    """>" & _
                    "<" & splitAttr & "/>" & _
                    Exit For
                End If

            GetxDomIndex = i

        End Function
    End Class

    'class used for testing
    'exclude from project if plan to build a DLL
    Public Class TestXMLTransformation

        Public Shared Sub Main()
            Dim startTime As DateTime
            Dim endTime As DateTime
            Dim xDOMIn As New XmlDocument
            Dim sourceFileNameDlg As _
                New System.Windows.Forms.OpenFileDialog
            Dim result As DialogResult
            Dim outputPath As String

                'setting up open file dialog
                sourceFileNameDlg.Title = _
                    "Select source XML file to convert to XML Tree:"
                sourceFileNameDlg.Filter = "XML files (*.xml)|*.xml"

                result = sourceFileNameDlg.ShowDialog()

                If result = DialogResult.OK Then
                    Dim fileName As String
                    fileName = sourceFileNameDlg.FileName
                    'output files will be written in the same 
                    'folder as the source file.
                    'NOTE: make sure that the output file names 
                    'do not overwrite source file!
                    outputPath = fileName.Substring(0, _
                                 fileName.LastIndexOf("\") + 1)

                    'reading in possible defaults for XML file processing
                    'processing first data row
                    'uniform file structure is essential for successful 
                    'file processing
                    'Underlying assumptions:
                    '1)We are processing SQL XML ..FOR XML RAW output
                    'that by definition will have <row ../> element
                    '2)Dimensions are hierarchical and go from the highest
                    'to lowest
                    Dim firstDataRowNode As XmlNode
                    firstDataRowNode = xDOMIn.SelectSingleNode("//row")

                    'assuming first attribute to be used for the file
                    Dim splitAttr As String
                    splitAttr = firstDataRowNode.Attributes(0).Name
                    splitAttr = InputBox("Input attribute name to " & _
                            "use for source file multi-file split." & _
                            vbCr & vbLf & vbCr & vbLf & _
                            "(NOTE: " & _
                            "1. Attribute names are case sensitive. " & _
                            "2. Input top level dimension attribute " & _
                            "name even if not splitting " & _
                            "into multiple files.)", _
                            "Split Attribute Name Input", splitAttr)

                    'assuming last attribute to be used for Data Value
                    Dim dataValueAttr As String
                    dataValueAttr = firstDataRowNode.Attributes( _
                            firstDataRowNode.Attributes.Count - 1).Name
                    dataValueAttr = InputBox("Input attribute name " & _
                            "for data value element." & _
                            vbCr & vbLf & vbCr & vbLf & _
                            "(NOTE: individual record " & _
                            "parsing stops after this " & _
                            "attribute and the value of " & _
                            "this attribute will be " & _
                            "associated with already " & _
                            "processed dimension elements " & _
                            "of the current row.)", _
                            "Data Value Attribute Name Input", _

                    'measuring total transformation time
                    startTime = startTime.Now()

                    'NOTE: XML is case sensitive, so attribute
                    'names passed to the function
                    'must match source XML file attributes case
                    RawXMLToXMLTree.TransformXMLDom(xDOMIn, _
                                    outputPath, splitAttr, dataValueAttr)

                    MsgBox("Time: " & endTime.Now(). _
                                    Subtract(startTime). _
                                    ToString(), _
                                    MsgBoxStyle.Information, _
                                    "Elapsed Time:")
                End If

            Catch e As Exception
                MsgBox(e.Message, MsgBoxStyle.Exclamation, _
                       "Error in processing XML file:")
            End Try
        End Sub
    End Class

End Namespace

C# Example

using System;
using System.Windows.Forms;
using System.Xml;  

namespace CustomXMLTransform 
    internal class xDOMCollection 
        public string Name; 
        public XmlDocument xDOM = new XmlDocument(); 

    public class RawXMLToXMLTree 
        public const int FILE_COUNT = 50; 
        internal static xDOMCollection[] xDOMOut = 
                         new xDOMCollection[FILE_COUNT]; 
        public static XmlNode xmlKeyNode;    

        public static void TransformXMLDom(
                            XmlDocument xDOMIn,
                            string outputPath, 
                            string splitAttr, 
                            string dataValueAttr) 
            XmlNodeList rowNodeList;               
            string curNode; 
            string prevNode; 
            string curElement; 
            int  xDOMIndex=0;  

            for (int i = 0; i < FILE_COUNT; i++) 
                RawXMLToXMLTree.xDOMOut[i] = new xDOMCollection(); 

            rowNodeList = xDOMIn.SelectNodes("//row");                   

            for (int i = 0; i < rowNodeList.Count; i++) 
                //each output file will contain DocumentElement ROOT
                prevNode = "ROOT"; 
                for (int ii = 0; 
                    ii < rowNodeList[i].Attributes.Count;
                    if (rowNodeList[i].Attributes.Item(ii)
                        xDOMIndex = RawXMLToXMLTree

                    if (rowNodeList[i].Attributes[ii]
                              .Name != dataValueAttr) 
                        curElement = rowNodeList[i].Attributes[ii].Value;  
                        curNode = prevNode + "/" + curElement; 
                        RawXMLToXMLTree.xmlKeyNode = 
                                .GetNode(ref xDOMIn, 
                            prevNode, curNode, curElement);
                        prevNode = curNode; 
                        RawXMLToXMLTree.xmlKeyNode.InnerText = 

            for (int i = 0; i < FILE_COUNT; i++) 
                if (xDOMOut[i].Name == null) 
                xDOMOut[i].xDOM.Save(outputPath + xDOMOut[i]
                    .Name + "_XLSMAP.xml"); 

        static XmlNode GetNode(ref XmlDocument xDOMIn, 
                               XmlDocument xDOMOut,
                               string prevNode, 
                               string curNode, 
                               string curElement)             
            XmlNode activeNode; 
            XmlNode nodeOut;                   

            activeNode = xDOMOut.SelectSingleNode(curNode); 
            if (activeNode == null) 
                activeNode = xDOMOut.SelectSingleNode(prevNode); 
                nodeOut = xDOMOut.CreateNode(XmlNodeType.Element, 
                    curElement, ""); 
                activeNode = activeNode.AppendChild(nodeOut); 
            return activeNode;

        static int GetxDomIndex(string splitAttr) 
            int i=0;                  

                if (RawXMLToXMLTree.xDOMOut[i].Name == splitAttr) 
                    return i; 
                else if (RawXMLToXMLTree.xDOMOut[i].Name == null) 
                    RawXMLToXMLTree.xDOMOut[i].Name = splitAttr; 
                    "<?xml version=\"1.0\" encoding=\"UTF-8" + 
                    "\" standalone=\"yes\"?>"+
                    "<ROOT xmlns:xsi="+
                    "\"\">" + 
                    "<" + splitAttr + "/>" + "</ROOT>");
                    return i; 
            } while(i < FILE_COUNT);
            return i; 

    public class TestXMLTransformation 
        public static void Main() 
            DateTime startTime; 
            XmlDocument xDOMIn = new XmlDocument(); 
            System.Windows.Forms.OpenFileDialog sourceFileNameDlg =
                new System.Windows.Forms.OpenFileDialog(); 
            DialogResult result; 
            string outputPath; 
            sourceFileNameDlg.Title = 
                "Select source XML file to convert to XML Tree:"; 
            sourceFileNameDlg.Filter = "XML files (*.xml)|*.xml"; 
            result = sourceFileNameDlg.ShowDialog(); 
            if (result == DialogResult.OK) 
                string fileName; 
                fileName = sourceFileNameDlg.FileName; 
                outputPath = fileName.Substring(0, 
                    fileName.LastIndexOf("\\") + 1); 
                XmlNode firstDataRowNode; 
                firstDataRowNode = xDOMIn.SelectSingleNode("//row"); 
                string splitAttr; 
                splitAttr = firstDataRowNode.Attributes[0].Name; 
                string dataValueAttr; 
                dataValueAttr = firstDataRowNode
                    .Attributes[firstDataRowNode.Attributes.Count - 1]

                result=MessageBox.Show("File Split Attribute: " + 
                    splitAttr + "\n" +
                    "Data Value Attribute: " + dataValueAttr + "\n\n" +
                    "Continue?","XML File Attributes",

                if(result==DialogResult.Cancel) return;                        

                startTime = DateTime.Now; 
                MessageBox.Show("Time: " + 
                    "Elapsed Time:",    

C# Example (Additional Notes)

The C# sample code does not have code comments. The code comments for Visual Basic .NET classes and methods with the same names are applicable for the C# code. One exception: C# does not support InputBox, so the Main method in TestXMLTransformation does not implement it. However, methods in the class RawXMLToXMLTree will behave identically.

Exploring the XML Map

You can use the previous four-dimension source XML sample file to test the code. After you run the sample file, you will find the [split dimension attribute value]_XLSMAP.XML file in the same folder as the source file. (If you are using the four-dimension sample, the output file name will be Client_XLSMAP.XML.) To test, open the Data menu, select XML and then Import, and follow the prompts. If you select XML Source instead, you will see the XML Source window as shown in Figure 1. You can drag individual XML values anywhere on the spreadsheet. The list object for XML files with repeating elements will not give you the same flexibility.

Mapping the spreadsheet cells by using the XML Source window (Click to see larger image)

Figure 1. Mapping the spreadsheet cells by using the XML Source window (Click to see larger image)

Now that you have mapped the spreadsheet cells, you can move data cells around, map and unmap elements, and import and export data out of the map in the same format as the [split dimension attribute value]_XLSMAP.XML file.

Manually Importing and Exporting XML Data

In Excel, open the Data menu, select XML and then Export to save mapped cell data as an XML document file. To refresh your spreadsheet from an XML data file, open the Data menu, select XML and then Import, and browse for an XML document conforming to your XML map schema.


The sample code in this article is generic and can be used for most scenarios requiring support of flexible data-transfer processes between SQL Server and an Excel spreadsheet. Currently we use this code in a project called "The Rhythm of the Business 2.0," which supports a flexible and constantly changing Excel template used by more than 1,000 Microsoft employees to assess the entire company's performance on a monthly basis. We process between 6,000 and 10,000 records on each data refresh on the client computer. The transformation process takes approximately 0.3–0.6 seconds. This time includes writing 15 XML output files to disk for data caching and depends on CPU speed (3GHz in our case). Our code is efficient and still very flexible.

Here are the key benefits of our approach: It supports any number of dimensions, and the sort order of the XML input is not important. In our previous article, Part 1: Automating the XML Data Mapping Process in Excel 2003, we described how to automate the process of single-cell XML mapping in the spreadsheet. Our next article provides a solution for harvesting calculated cell values that do not have XML mapping.

Additional Resources

The following content can help you develop more custom XML solutions using Excel:

© 2016 Microsoft