none
sql server 下xml to table的问题 RRS feed

  • 问题

  • SQL SERVER 2008 R2中,表A中有一字段类型XML;

    XML值如下:

    <?xml version="1.0"?>
    <?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"
     xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     xmlns:fn="http://www.w3.org/2005/xpath-functions"
     xmlns:xdt="http://www.w3.org/2005/xpath-datatypes">
     <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author>lhlin</Author>
      <LastAuthor>Windows 用户</LastAuthor>
      <Created>2011-09-20T02:11:11Z</Created>
      <LastSaved>2012-05-09T06:04:44Z</LastSaved>
      <Version>12.00</Version>
     </DocumentProperties>
     <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
      <Colors>
       <Color>
        <Index>16</Index>
        <RGB>#8080FF</RGB>
       </Color>
       <Color>
        <Index>17</Index>
        <RGB>#802060</RGB>
       </Color>
       <Color>
        <Index>18</Index>
        <RGB>#FFFFC0</RGB>
       </Color>
       <Color>
        <Index>19</Index>
        <RGB>#A0E0E0</RGB>
       </Color>
       <Color>
        <Index>20</Index>
        <RGB>#600080</RGB>
       </Color>
       <Color>
        <Index>22</Index>
        <RGB>#0080C0</RGB>
       </Color>
       <Color>
        <Index>23</Index>
        <RGB>#C0C0FF</RGB>
       </Color>
       <Color>
        <Index>33</Index>
        <RGB>#69FFFF</RGB>
       </Color>
       <Color>
        <Index>36</Index>
        <RGB>#A6CAF0</RGB>
       </Color>
       <Color>
        <Index>37</Index>
        <RGB>#CC9CCC</RGB>
       </Color>
       <Color>
        <Index>39</Index>
        <RGB>#E3E3E3</RGB>
       </Color>
       <Color>
        <Index>42</Index>
        <RGB>#339933</RGB>
       </Color>
       <Color>
        <Index>43</Index>
        <RGB>#999933</RGB>
       </Color>
       <Color>
        <Index>44</Index>
        <RGB>#996633</RGB>
       </Color>
       <Color>
        <Index>45</Index>
        <RGB>#996666</RGB>
       </Color>
       <Color>
        <Index>48</Index>
        <RGB>#3333CC</RGB>
       </Color>
       <Color>
        <Index>49</Index>
        <RGB>#336666</RGB>
       </Color>
       <Color>
        <Index>52</Index>
        <RGB>#663300</RGB>
       </Color>
       <Color>
        <Index>55</Index>
        <RGB>#424242</RGB>
       </Color>
      </Colors>
     </OfficeDocumentSettings>
     <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <WindowHeight>8730</WindowHeight>
      <WindowWidth>1980</WindowWidth>
      <WindowTopX>45</WindowTopX>
      <WindowTopY>45</WindowTopY>
      <ProtectStructure>False</ProtectStructure>
      <ProtectWindows>False</ProtectWindows>
     </ExcelWorkbook>
     <Styles>
      <Style ss:ID="Default" ss:Name="Normal">
       <Alignment ss:Vertical="Center"/>
       <Borders/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
       <Interior/>
       <NumberFormat/>
       <Protection/>
      </Style>
      <Style ss:ID="s62">
       <Alignment ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11"/>
      </Style>
      <Style ss:ID="s63">
       <Alignment ss:Vertical="Center"/>
       <Borders/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>
       <Interior/>
       <NumberFormat ss:Format="@"/>
       <Protection/>
      </Style>
      <Style ss:ID="s64">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134"/>
       <NumberFormat ss:Format="@"/>
      </Style>
      <Style ss:ID="s65">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Borders/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>
       <Interior/>
       <NumberFormat ss:Format="@"/>
       <Protection/>
      </Style>
      <Style ss:ID="s66">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134"/>
      </Style>
      <Style ss:ID="s68">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134"/>
       <NumberFormat/>
      </Style>
     </Styles>
     <Worksheet ss:Name="aa">
      <Table ss:ExpandedColumnCount="14" ss:ExpandedRowCount="5" x:FullColumns="1"
       x:FullRows="1" ss:StyleID="s62" ss:DefaultColumnWidth="54"
       ss:DefaultRowHeight="13.5">
       <Column ss:Index="2" ss:StyleID="s62" ss:Width="96.75"/>
       <Column ss:StyleID="s62" ss:Width="67.5"/>
       <Column ss:StyleID="s62" ss:Width="45.75" ss:Span="1"/>
       <Column ss:Index="6" ss:StyleID="s62" ss:Width="91.5"/>
       <Column ss:Index="9" ss:StyleID="s62" ss:Width="24.75"/>
       <Row ss:AutoFitHeight="0" ss:Height="12.75" ss:StyleID="s63">
        <Cell ss:StyleID="s64"><Data ss:Type="String">Raw Materials</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">**</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">a</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">b</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">c</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">d</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">e</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">f</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">g</Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
       </Row>
       <Row ss:AutoFitHeight="0">
        <Cell ss:StyleID="s66"><Data ss:Type="String">ARA</Data></Cell>
        <Cell ss:StyleID="s66"><Data ss:Type="String">沙轻-沙特</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">5.4459999999999997</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">5445.6</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0.17</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:StyleID="s66"><Data ss:Type="String">JIK</Data></Cell>
       </Row>   
      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
       <Unsynced/>
       <Print>
        <ValidPrinterInfo/>
        <PaperSizeIndex>9</PaperSizeIndex>
        <HorizontalResolution>200</HorizontalResolution>
        <VerticalResolution>200</VerticalResolution>
       </Print>
       <Selected/>
       <Panes>
        <Pane>
         <Number>3</Number>
         <ActiveRow>8</ActiveRow>
         <ActiveCol>2</ActiveCol>
        </Pane>
       </Panes>
       <ProtectObjects>False</ProtectObjects>
       <ProtectScenarios>False</ProtectScenarios>
      </WorksheetOptions>
     </Worksheet>
    </Workbook>
    

    我想在SQL 中直接查询,用OPENXML,可是写不出我想要的结果;

    我想在SQL SERVER中查询的结果如下:

    Materials ** a b c d e f g
    ARA 沙轻-沙特 5.446 0 0 5445.6 0.17 0 JIK

    请问这个SQL怎么写啊?


    大其心,可容天下之物; 虚其心,可受天下之善;

    2012年9月29日 7:29

答案

  • DECLARE @xml xml;
    SET @xml = N'<?xml version="1.0"?>
    <?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"
     xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     xmlns:fn="http://www.w3.org/2005/xpath-functions"
     xmlns:xdt="http://www.w3.org/2005/xpath-datatypes">
     <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author>lhlin</Author>
      <LastAuthor>Windows 用户</LastAuthor>
      <Created>2011-09-20T02:11:11Z</Created>
      <LastSaved>2012-05-09T06:04:44Z</LastSaved>
      <Version>12.00</Version>
     </DocumentProperties>
     <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
      <Colors>
       <Color>
        <Index>16</Index>
        <RGB>#8080FF</RGB>
       </Color>
       <Color>
        <Index>17</Index>
        <RGB>#802060</RGB>
       </Color>
       <Color>
        <Index>18</Index>
        <RGB>#FFFFC0</RGB>
       </Color>
       <Color>
        <Index>19</Index>
        <RGB>#A0E0E0</RGB>
       </Color>
       <Color>
        <Index>20</Index>
        <RGB>#600080</RGB>
       </Color>
       <Color>
        <Index>22</Index>
        <RGB>#0080C0</RGB>
       </Color>
       <Color>
        <Index>23</Index>
        <RGB>#C0C0FF</RGB>
       </Color>
       <Color>
        <Index>33</Index>
        <RGB>#69FFFF</RGB>
       </Color>
       <Color>
        <Index>36</Index>
        <RGB>#A6CAF0</RGB>
       </Color>
       <Color>
        <Index>37</Index>
        <RGB>#CC9CCC</RGB>
       </Color>
       <Color>
        <Index>39</Index>
        <RGB>#E3E3E3</RGB>
       </Color>
       <Color>
        <Index>42</Index>
        <RGB>#339933</RGB>
       </Color>
       <Color>
        <Index>43</Index>
        <RGB>#999933</RGB>
       </Color>
       <Color>
        <Index>44</Index>
        <RGB>#996633</RGB>
       </Color>
       <Color>
        <Index>45</Index>
        <RGB>#996666</RGB>
       </Color>
       <Color>
        <Index>48</Index>
        <RGB>#3333CC</RGB>
       </Color>
       <Color>
        <Index>49</Index>
        <RGB>#336666</RGB>
       </Color>
       <Color>
        <Index>52</Index>
        <RGB>#663300</RGB>
       </Color>
       <Color>
        <Index>55</Index>
        <RGB>#424242</RGB>
       </Color>
      </Colors>
     </OfficeDocumentSettings>
     <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <WindowHeight>8730</WindowHeight>
      <WindowWidth>1980</WindowWidth>
      <WindowTopX>45</WindowTopX>
      <WindowTopY>45</WindowTopY>
      <ProtectStructure>False</ProtectStructure>
      <ProtectWindows>False</ProtectWindows>
     </ExcelWorkbook>
     <Styles>
      <Style ss:ID="Default" ss:Name="Normal">
       <Alignment ss:Vertical="Center"/>
       <Borders/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
       <Interior/>
       <NumberFormat/>
       <Protection/>
      </Style>
      <Style ss:ID="s62">
       <Alignment ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11"/>
      </Style>
      <Style ss:ID="s63">
       <Alignment ss:Vertical="Center"/>
       <Borders/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>
       <Interior/>
       <NumberFormat ss:Format="@"/>
       <Protection/>
      </Style>
      <Style ss:ID="s64">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134"/>
       <NumberFormat ss:Format="@"/>
      </Style>
      <Style ss:ID="s65">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Borders/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>
       <Interior/>
       <NumberFormat ss:Format="@"/>
       <Protection/>
      </Style>
      <Style ss:ID="s66">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134"/>
      </Style>
      <Style ss:ID="s68">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134"/>
       <NumberFormat/>
      </Style>
     </Styles>
     <Worksheet ss:Name="aa">
      <Table ss:ExpandedColumnCount="14" ss:ExpandedRowCount="5" x:FullColumns="1"
       x:FullRows="1" ss:StyleID="s62" ss:DefaultColumnWidth="54"
       ss:DefaultRowHeight="13.5">
       <Column ss:Index="2" ss:StyleID="s62" ss:Width="96.75"/>
       <Column ss:StyleID="s62" ss:Width="67.5"/>
       <Column ss:StyleID="s62" ss:Width="45.75" ss:Span="1"/>
       <Column ss:Index="6" ss:StyleID="s62" ss:Width="91.5"/>
       <Column ss:Index="9" ss:StyleID="s62" ss:Width="24.75"/>
       <Row ss:AutoFitHeight="0" ss:Height="12.75" ss:StyleID="s63">
        <Cell ss:StyleID="s64"><Data ss:Type="String">Raw Materials</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">**</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">a</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">b</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">c</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">d</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">e</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">f</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">g</Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
       </Row>
       <Row ss:AutoFitHeight="0">
        <Cell ss:StyleID="s66"><Data ss:Type="String">ARA</Data></Cell>
        <Cell ss:StyleID="s66"><Data ss:Type="String">沙轻-沙特</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">5.4459999999999997</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">5445.6</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0.17</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:StyleID="s66"><Data ss:Type="String">JIK</Data></Cell>
       </Row>   
      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
       <Unsynced/>
       <Print>
        <ValidPrinterInfo/>
        <PaperSizeIndex>9</PaperSizeIndex>
        <HorizontalResolution>200</HorizontalResolution>
        <VerticalResolution>200</VerticalResolution>
       </Print>
       <Selected/>
       <Panes>
        <Pane>
         <Number>3</Number>
         <ActiveRow>8</ActiveRow>
         <ActiveCol>2</ActiveCol>
        </Pane>
       </Panes>
       <ProtectObjects>False</ProtectObjects>
       <ProtectScenarios>False</ProtectScenarios>
      </WorksheetOptions>
     </Worksheet>
    </Workbook>
    ';
    WITH
    XMLNAMESPACES(
    	DEFAULT N'urn:schemas-microsoft-com:office:spreadsheet'
    )
    SELECT
    	T.c.value('(Cell/Data/text())[1]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[2]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[3]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[4]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[5]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[6]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[7]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[8]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[9]', 'nvarchar(100)')
    FROM @xml.nodes('/Workbook/Worksheet/Table/Row') T(c)
    ;

    2012年10月8日 1:18
  • 如要要求列名,那么只能用动态的了,参考下面这个(xml变量定义与前面一样)

    DECLARE
    	@sql nvarchar(max),
    	@position int
    ;
    SELECT
    	@sql = N'',
    	@position = 0
    ;
    WITH
    XMLNAMESPACES(
    	DEFAULT N'urn:schemas-microsoft-com:office:spreadsheet'
    )
    SELECT
    	@position = @position + 1,
    	@sql = @sql + N',
    	' + QUOTENAME(T.c.value('(.)[1]', 'nvarchar(100)'))
    	+ N' = T.c.value(''(Cell/Data/text())[' + RTRIM(@position) + N']'', ''nvarchar(100)'')'
    FROM @xml.nodes('/Workbook/Worksheet/Table/Row[position()=1]/Cell/Data/text()') T(c)
    ;
    SET @sql = N'
    WITH
    XMLNAMESPACES(
    	DEFAULT N''urn:schemas-microsoft-com:office:spreadsheet''
    )
    SELECT'
    	+ STUFF(@sql, 1, 1, N'')
    	+ N'
    FROM @xml.nodes(''/Workbook/Worksheet/Table/Row[position()>1]'') T(c)
    ';
    print @sql;
    EXEC sp_executesql
    	@sql,
    	N'
    		@xml xml
    	',
    	@xml
    ;

    2012年10月8日 1:36

全部回复

  • 你要想直接得到这个结果,需要用value(), nodes()这些函数。

    想不想时已是想,不如不想都不想。

    2012年9月29日 14:14
    版主
  • 关注一下

    给我写信: QQ我:点击这里给我发消息

    2012年9月29日 15:57
  • 能写个示例看看不

    大其心,可容天下之物; 虚其心,可受天下之善;

    2012年10月8日 0:37
  • DECLARE @xml xml;
    SET @xml = N'<?xml version="1.0"?>
    <?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"
     xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     xmlns:fn="http://www.w3.org/2005/xpath-functions"
     xmlns:xdt="http://www.w3.org/2005/xpath-datatypes">
     <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author>lhlin</Author>
      <LastAuthor>Windows 用户</LastAuthor>
      <Created>2011-09-20T02:11:11Z</Created>
      <LastSaved>2012-05-09T06:04:44Z</LastSaved>
      <Version>12.00</Version>
     </DocumentProperties>
     <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
      <Colors>
       <Color>
        <Index>16</Index>
        <RGB>#8080FF</RGB>
       </Color>
       <Color>
        <Index>17</Index>
        <RGB>#802060</RGB>
       </Color>
       <Color>
        <Index>18</Index>
        <RGB>#FFFFC0</RGB>
       </Color>
       <Color>
        <Index>19</Index>
        <RGB>#A0E0E0</RGB>
       </Color>
       <Color>
        <Index>20</Index>
        <RGB>#600080</RGB>
       </Color>
       <Color>
        <Index>22</Index>
        <RGB>#0080C0</RGB>
       </Color>
       <Color>
        <Index>23</Index>
        <RGB>#C0C0FF</RGB>
       </Color>
       <Color>
        <Index>33</Index>
        <RGB>#69FFFF</RGB>
       </Color>
       <Color>
        <Index>36</Index>
        <RGB>#A6CAF0</RGB>
       </Color>
       <Color>
        <Index>37</Index>
        <RGB>#CC9CCC</RGB>
       </Color>
       <Color>
        <Index>39</Index>
        <RGB>#E3E3E3</RGB>
       </Color>
       <Color>
        <Index>42</Index>
        <RGB>#339933</RGB>
       </Color>
       <Color>
        <Index>43</Index>
        <RGB>#999933</RGB>
       </Color>
       <Color>
        <Index>44</Index>
        <RGB>#996633</RGB>
       </Color>
       <Color>
        <Index>45</Index>
        <RGB>#996666</RGB>
       </Color>
       <Color>
        <Index>48</Index>
        <RGB>#3333CC</RGB>
       </Color>
       <Color>
        <Index>49</Index>
        <RGB>#336666</RGB>
       </Color>
       <Color>
        <Index>52</Index>
        <RGB>#663300</RGB>
       </Color>
       <Color>
        <Index>55</Index>
        <RGB>#424242</RGB>
       </Color>
      </Colors>
     </OfficeDocumentSettings>
     <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <WindowHeight>8730</WindowHeight>
      <WindowWidth>1980</WindowWidth>
      <WindowTopX>45</WindowTopX>
      <WindowTopY>45</WindowTopY>
      <ProtectStructure>False</ProtectStructure>
      <ProtectWindows>False</ProtectWindows>
     </ExcelWorkbook>
     <Styles>
      <Style ss:ID="Default" ss:Name="Normal">
       <Alignment ss:Vertical="Center"/>
       <Borders/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
       <Interior/>
       <NumberFormat/>
       <Protection/>
      </Style>
      <Style ss:ID="s62">
       <Alignment ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11"/>
      </Style>
      <Style ss:ID="s63">
       <Alignment ss:Vertical="Center"/>
       <Borders/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>
       <Interior/>
       <NumberFormat ss:Format="@"/>
       <Protection/>
      </Style>
      <Style ss:ID="s64">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134"/>
       <NumberFormat ss:Format="@"/>
      </Style>
      <Style ss:ID="s65">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Borders/>
       <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>
       <Interior/>
       <NumberFormat ss:Format="@"/>
       <Protection/>
      </Style>
      <Style ss:ID="s66">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134"/>
      </Style>
      <Style ss:ID="s68">
       <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
       <Font ss:FontName="宋体" x:CharSet="134"/>
       <NumberFormat/>
      </Style>
     </Styles>
     <Worksheet ss:Name="aa">
      <Table ss:ExpandedColumnCount="14" ss:ExpandedRowCount="5" x:FullColumns="1"
       x:FullRows="1" ss:StyleID="s62" ss:DefaultColumnWidth="54"
       ss:DefaultRowHeight="13.5">
       <Column ss:Index="2" ss:StyleID="s62" ss:Width="96.75"/>
       <Column ss:StyleID="s62" ss:Width="67.5"/>
       <Column ss:StyleID="s62" ss:Width="45.75" ss:Span="1"/>
       <Column ss:Index="6" ss:StyleID="s62" ss:Width="91.5"/>
       <Column ss:Index="9" ss:StyleID="s62" ss:Width="24.75"/>
       <Row ss:AutoFitHeight="0" ss:Height="12.75" ss:StyleID="s63">
        <Cell ss:StyleID="s64"><Data ss:Type="String">Raw Materials</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">**</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">a</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">b</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">c</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">d</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">e</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">f</Data></Cell>
        <Cell ss:StyleID="s64"><Data ss:Type="String">g</Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
        <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
       </Row>
       <Row ss:AutoFitHeight="0">
        <Cell ss:StyleID="s66"><Data ss:Type="String">ARA</Data></Cell>
        <Cell ss:StyleID="s66"><Data ss:Type="String">沙轻-沙特</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">5.4459999999999997</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">5445.6</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0.17</Data></Cell>
        <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:StyleID="s66"><Data ss:Type="String">JIK</Data></Cell>
       </Row>   
      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
       <Unsynced/>
       <Print>
        <ValidPrinterInfo/>
        <PaperSizeIndex>9</PaperSizeIndex>
        <HorizontalResolution>200</HorizontalResolution>
        <VerticalResolution>200</VerticalResolution>
       </Print>
       <Selected/>
       <Panes>
        <Pane>
         <Number>3</Number>
         <ActiveRow>8</ActiveRow>
         <ActiveCol>2</ActiveCol>
        </Pane>
       </Panes>
       <ProtectObjects>False</ProtectObjects>
       <ProtectScenarios>False</ProtectScenarios>
      </WorksheetOptions>
     </Worksheet>
    </Workbook>
    ';
    WITH
    XMLNAMESPACES(
    	DEFAULT N'urn:schemas-microsoft-com:office:spreadsheet'
    )
    SELECT
    	T.c.value('(Cell/Data/text())[1]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[2]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[3]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[4]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[5]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[6]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[7]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[8]', 'nvarchar(100)'),
    	T.c.value('(Cell/Data/text())[9]', 'nvarchar(100)')
    FROM @xml.nodes('/Workbook/Worksheet/Table/Row') T(c)
    ;

    2012年10月8日 1:18
  • 如要要求列名,那么只能用动态的了,参考下面这个(xml变量定义与前面一样)

    DECLARE
    	@sql nvarchar(max),
    	@position int
    ;
    SELECT
    	@sql = N'',
    	@position = 0
    ;
    WITH
    XMLNAMESPACES(
    	DEFAULT N'urn:schemas-microsoft-com:office:spreadsheet'
    )
    SELECT
    	@position = @position + 1,
    	@sql = @sql + N',
    	' + QUOTENAME(T.c.value('(.)[1]', 'nvarchar(100)'))
    	+ N' = T.c.value(''(Cell/Data/text())[' + RTRIM(@position) + N']'', ''nvarchar(100)'')'
    FROM @xml.nodes('/Workbook/Worksheet/Table/Row[position()=1]/Cell/Data/text()') T(c)
    ;
    SET @sql = N'
    WITH
    XMLNAMESPACES(
    	DEFAULT N''urn:schemas-microsoft-com:office:spreadsheet''
    )
    SELECT'
    	+ STUFF(@sql, 1, 1, N'')
    	+ N'
    FROM @xml.nodes(''/Workbook/Worksheet/Table/Row[position()>1]'') T(c)
    ';
    print @sql;
    EXEC sp_executesql
    	@sql,
    	N'
    		@xml xml
    	',
    	@xml
    ;

    2012年10月8日 1:36
  • 你是真正的高手


    大其心,可容天下之物; 虚其心,可受天下之善;

    2012年10月10日 13:55