积极答复者
sql server 下xml to table的问题

问题
-
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怎么写啊?
大其心,可容天下之物; 虚其心,可受天下之善;
- 已更改类型 maco wangModerator 2012年9月29日 11:30 非讨论性质
答案
-
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) ;
- 已标记为答案 Molly Chen_Moderator 2012年10月9日 2:28
-
如要要求列名,那么只能用动态的了,参考下面这个(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 ;
- 已标记为答案 Molly Chen_Moderator 2012年10月9日 2:28
全部回复
-
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) ;
- 已标记为答案 Molly Chen_Moderator 2012年10月9日 2:28
-
如要要求列名,那么只能用动态的了,参考下面这个(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 ;
- 已标记为答案 Molly Chen_Moderator 2012年10月9日 2:28