How to Import From the Xlsx-XML(XLSX file Saved in XML Format) Data in to SQL Server RRS feed

  • Question

  • Hi  All ,

    My name is Srinivas.I have one question ?

     Any one please help how to read the data from Xlsx-XML Template ( Xlsx Template is saved in XML Formate) from SQL Server query . If we Open this template It is Opened in Xlsx format and it does'nt show any tags or cells.

    For Ex :   Like following ex  read data from  Xlsx file using OPENROWSET

    Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\69\Test.xlsx;HDR=YES',
                    'SELECT * FROM [Sheet1$]') Spread.

    <Cell ss:StyleID="s71"><Data ss:Type="String">Title</Data><NamedCell
        <Cell ss:StyleID="s72"><Data ss:Type="String">Type</Data><NamedCell
        <Cell ss:StyleID="s72"><Data ss:Type="String">Sub-Type</Data><NamedCell
        <Cell ss:StyleID="s71"><Data ss:Type="String">Reference</Data><NamedCell
        <Cell ss:StyleID="s73"><Data ss:Type="String">Country</Data><NamedCell
        <Cell ss:StyleID="s74"><Data ss:Type="String">TestFirm</Data><NamedCell
        <Cell ss:StyleID="s74"><Data ss:Type="String">Manager</Data><NamedCell
        <Cell><Data ss:Type="String"> Manager2</Data><NamedCell
        <Cell ss:StyleID="s74"><Data ss:Type="String">Party</Data><NamedCell
       <Row ss:AutoFitHeight="0" ss:Height="27.5625">
        <Cell ss:StyleID="s79"><Data ss:Type="String">Test Name</Data></Cell>
        <Cell><Data ss:Type="String">Test Type</Data></Cell>
        <Cell><Data ss:Type="String">TestSbtype</Data></Cell>
        <Cell ss:StyleID="s79"><Data ss:Type="String">Reference67854h</Data></Cell>
        <Cell ss:StyleID="s80"><Data ss:Type="String">USA</Data></Cell>
        <Cell ss:StyleID="s80"><Data ss:Type="String">jk0001</Data></Cell>
        <Cell ss:StyleID="s82"><Data ss:Type="String">test@gmail.com</Data></Cell>
        <Cell ss:StyleID="s83"><Data ss:Type="String">test@gmail.com</Data></Cell>
        <Cell ss:StyleID="s80"><Data ss:Type="String">UNKNOWN</Data></Cell>
        <Cell ss:StyleID="s84"><Data ss:Type="String">vkv0058</Data></Cell>
        <Cell ss:StyleID="s80"><Data ss:Type="String">CLOSED PER MM 8/15/2006 EM</Data></Cell>
         <Cell ss:StyleID="s86"><Data ss:Type="DateTime">2008-02-26T00:00:00.000</Data></Cell>

    s there any solution ... for this .. ( It is not a  XML Doc.... XLSX s saved in XML 2003 format) If we Open this It Opens in like a Xlsx doc...not showing any tags or elements...

    Thanks in advance...

    Monday, August 26, 2013 7:15 AM


  • Hi,

    I suggest you to take a look at SSIS (SQL Server Integration Services), it can easily import data from Excel (whether XLS, XLSX, XLSB, etc) into SQL Server.

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Proposed as answer by Sofiya Li Tuesday, August 27, 2013 1:05 AM
    • Marked as answer by Fanny Liu Tuesday, September 3, 2013 1:43 PM
    Monday, August 26, 2013 12:18 PM