当前位置 > 首页 > Sql

sql server 下xml to table的问题

9/29/2012 3:08:00 PM来源:Sql

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&quo