Read data from Xlsx-XML Template from SQL Server


  • 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 .

    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

    Thanks in advance...

    Friday, August 23, 2013 8:36 AM

All replies

  • Hello,

    You can try to using OPENXML to import XML documents into an XML variable, or into a table that has XML columns, and then query data values from the XML variable or column.


    Fanny Liu

    Fanny Liu
    TechNet Community Support

    Saturday, August 24, 2013 9:55 AM
  • Hi Fanny Liu,

    Thanks for the suggestions. But i have a problem to read data.

    If i Open the document It is opened in the Xlsx format ( not in tags). If i am trying to Open in xml format It does'nt have the Root Tags.

    I am unable to

    SET @xmlDocument = N'<ROOT>
    <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
    <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
    <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
    <Customers CustomerID="XYZBB" ContactName="Steve"
    CompanyName="Company2">No Orders yet!
    </ROOT>';....................................... as per your reference..

    I have Open the sheet in notepad  ..see the following

    <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"></Data></Cell>
        <Cell ss:StyleID="s83"><Data ss:Type="String"></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>

    is 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...

    Sunday, August 25, 2013 1:33 PM