sql server 下xml to table的问题

Question

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怎么写啊？

---

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

Answer 1

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

---

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

Answer 2

关注一下

---

给我写信： QQ我：

Answer 3

能写个示例看看不

---

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

Answer 4

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)
;

Answer 5

如要要求列名，那么只能用动态的了，参考下面这个（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
;

Answer 6

你是真正的高手

---

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