SQL Server

Parse the XML of an Excel (XLS) Spreadsheet

See more XML Examples

Demonstrate how to access various items from the XML of an XLS (Excel) Spreadsheet. Our sample data is shown below, and can be downloaded from http://www.chilkatsoft.com/data/ndc_nhric_labeler_codes_04_29_2015.xls

<?xml version="1.0" version="1.0" encoding="utf-8" ?>
<mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
    <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Version>14.00</Version>
    </DocumentProperties>
    <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
        <AllowPNG />
    </OfficeDocumentSettings>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>7800</WindowHeight>
        <WindowWidth>12000</WindowWidth>
        <WindowTopX>105</WindowTopX>
        <WindowTopY>105</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
            <Alignment ss:Vertical="Top" />
            <Borders />
            <Font ss:FontName="Arial" />
            <Interior />
            <NumberFormat />
            <Protection />
        </Style>
        <Style ss:ID="s63">
            <Alignment ss:Vertical="Bottom" ss:WrapText="1" />
            <Font ss:FontName="Arial" x:Family="Swiss" ss:Bold="1" />
            <Interior ss:Color="#C0C0C0" ss:Pattern="Solid" />
        </Style>
        <Style ss:ID="s64">
            <NumberFormat ss:Format="@" />
        </Style>
    </Styles>
    <Worksheet ss:Name="NDCLabelerCode">
        <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="8186" x:FullColumns="1" x:FullRows="1">
            <Column ss:Index="2" ss:Width="580.5" />
            <Row ss:AutoFitHeight="0" ss:Height="38.25">
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">NDC Labeler Code</Data>
                </Cell>
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">Firm Name</Data>
                </Cell>
            </Row>
            <Row>
                <Cell ss:StyleID="s64">
                    <Data ss:Type="String">0002</Data>
                </Cell>
                <Cell ss:StyleID="s64">
                    <Data ss:Type="String">Eli Lilly and Company</Data>
                </Cell>
            </Row>
...

SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    DECLARE @xlsDoc int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xlsDoc OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @xlsDoc, 'LoadXmlFile', @success OUT, 'myFiles/ndc_nhric_labeler_codes_04_29_2015.xls'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xlsDoc, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xlsDoc
        RETURN
      END

    -- This example will access the following pieces of data:
    -- NDC Labeler Code, Firm Name, 0002, Eli Lilly and Company, ...

    -- First navigate to the Worksheet named "NDCLabelerCode"
    DECLARE @xWorksheet int
    EXEC sp_OAMethod @xlsDoc, 'GetChildWithAttr', @xWorksheet OUT, 'Worksheet', 'ss:Name', 'NDCLabelerCode'

    -- Next, get the Table node:
    DECLARE @x int
    EXEC sp_OAMethod @xWorksheet, 'GetChildWithTag', @x OUT, 'Table'

    -- How many immediate child nodes?
    DECLARE @numChildren int
    EXEC sp_OAGetProperty @x, 'NumChildren', @numChildren OUT

    -- Loop over each Row and access the cell contents.
    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= (@numChildren - 1)
      BEGIN

        -- Navigate without creating new XML objects
        EXEC sp_OAMethod @x, 'GetChild2', @success OUT, @i

        EXEC sp_OAMethod @x, 'TagEquals', @iTmp0 OUT, 'Row'
        IF @iTmp0
          BEGIN

            DECLARE @data0 nvarchar(4000)
            EXEC sp_OAMethod @x, 'ChilkatPath', @data0 OUT, 'Cell[0]|Data|*'

            PRINT @data0

            DECLARE @data1 nvarchar(4000)
            EXEC sp_OAMethod @x, 'ChilkatPath', @data1 OUT, 'Cell[1]|Data|*'

            PRINT @data1
          END

        -- Navigate back up.
        EXEC sp_OAMethod @x, 'GetParent2', @success OUT
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @x

    EXEC @hr = sp_OADestroy @xWorksheet


    EXEC @hr = sp_OADestroy @xlsDoc


END
GO