笔记59 sql server 下xml to table

笔记59 sql server 下xml to table

  1 --sql server 下xml to table
  2 DECLARE @xml xml;
  3 SET @xml = N'<?xml version="1.0"?>
  4 <?mso-application progid="Excel.Sheet"?>
  5 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  6 xmlns:o="urn:schemas-microsoft-com:office:office"
  7 xmlns:x="urn:schemas-microsoft-com:office:excel"
  8 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  9 xmlns:html="http://www.w3.org/TR/REC-html40"
 10 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 11 xmlns:fn="http://www.w3.org/2005/xpath-functions"
 12 xmlns:xdt="http://www.w3.org/2005/xpath-datatypes">
 13 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
 14   <Author>lhlin</Author>
 15   <LastAuthor>Windows 用户</LastAuthor>
 16   <Created>2011-09-20T02:11:11Z</Created>
 17   <LastSaved>2012-05-09T06:04:44Z</LastSaved>
 18   <Version>12.00</Version>
 19 </DocumentProperties>
 20 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
 21   <Colors>
 22    <Color>
 23     <Index>16</Index>
 24     <RGB>#8080FF</RGB>
 25    </Color>
 26    <Color>
 27     <Index>17</Index>
 28     <RGB>#802060</RGB>
 29    </Color>
 30    <Color>
 31     <Index>18</Index>
 32     <RGB>#FFFFC0</RGB>
 33    </Color>
 34    <Color>
 35     <Index>19</Index>
 36     <RGB>#A0E0E0</RGB>
 37    </Color>
 38    <Color>
 39     <Index>20</Index>
 40     <RGB>#600080</RGB>
 41    </Color>
 42    <Color>
 43     <Index>22</Index>
 44     <RGB>#0080C0</RGB>
 45    </Color>
 46    <Color>
 47     <Index>23</Index>
 48     <RGB>#C0C0FF</RGB>
 49    </Color>
 50    <Color>
 51     <Index>33</Index>
 52     <RGB>#69FFFF</RGB>
 53    </Color>
 54    <Color>
 55     <Index>36</Index>
 56     <RGB>#A6CAF0</RGB>
 57    </Color>
 58    <Color>
 59     <Index>37</Index>
 60     <RGB>#CC9CCC</RGB>
 61    </Color>
 62    <Color>
 63     <Index>39</Index>
 64     <RGB>#E3E3E3</RGB>
 65    </Color>
 66    <Color>
 67     <Index>42</Index>
 68     <RGB>#339933</RGB>
 69    </Color>
 70    <Color>
 71     <Index>43</Index>
 72     <RGB>#999933</RGB>
 73    </Color>
 74    <Color>
 75     <Index>44</Index>
 76     <RGB>#996633</RGB>
 77    </Color>
 78    <Color>
 79     <Index>45</Index>
 80     <RGB>#996666</RGB>
 81    </Color>
 82    <Color>
 83     <Index>48</Index>
 84     <RGB>#3333CC</RGB>
 85    </Color>
 86    <Color>
 87     <Index>49</Index>
 88     <RGB>#336666</RGB>
 89    </Color>
 90    <Color>
 91     <Index>52</Index>
 92     <RGB>#663300</RGB>
 93    </Color>
 94    <Color>
 95     <Index>55</Index>
 96     <RGB>#424242</RGB>
 97    </Color>
 98   </Colors>
 99 </OfficeDocumentSettings>
100 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
101   <WindowHeight>8730</WindowHeight>
102   <WindowWidth>1980</WindowWidth>
103   <WindowTopX>45</WindowTopX>
104   <WindowTopY>45</WindowTopY>
105   <ProtectStructure>False</ProtectStructure>
106   <ProtectWindows>False</ProtectWindows>
107 </ExcelWorkbook>
108 <Styles>
109   <Style ss:ID="Default" ss:Name="Normal">
110    <Alignment ss:Vertical="Center"/>
111    <Borders/>
112    <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
113    <Interior/>
114    <NumberFormat/>
115    <Protection/>
116   </Style>
117   <Style ss:ID="s62">
118    <Alignment ss:Vertical="Center"/>
119    <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11"/>
120   </Style>
121   <Style ss:ID="s63">
122    <Alignment ss:Vertical="Center"/>
123    <Borders/>
124    <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>
125    <Interior/>
126    <NumberFormat ss:Format="@"/>
127    <Protection/>
128   </Style>
129   <Style ss:ID="s64">
130    <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
131    <Font ss:FontName="宋体" x:CharSet="134"/>
132    <NumberFormat ss:Format="@"/>
133   </Style>
134   <Style ss:ID="s65">
135    <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
136    <Borders/>
137    <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/>
138    <Interior/>
139    <NumberFormat ss:Format="@"/>
140    <Protection/>
141   </Style>
142   <Style ss:ID="s66">
143    <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
144    <Font ss:FontName="宋体" x:CharSet="134"/>
145   </Style>
146   <Style ss:ID="s68">
147    <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
148    <Font ss:FontName="宋体" x:CharSet="134"/>
149    <NumberFormat/>
150   </Style>
151 </Styles>
152 <Worksheet ss:Name="aa">
153   <Table ss:ExpandedColumnCount="14" ss:ExpandedRowCount="5" x:FullColumns="1"
154    x:FullRows="1" ss:StyleID="s62" ss:DefaultColumnWidth="54"
155    ss:DefaultRowHeight="13.5">
156    <Column ss:Index="2" ss:StyleID="s62" ss:Width="96.75"/>
157    <Column ss:StyleID="s62" ss:Width="67.5"/>
158    <Column ss:StyleID="s62" ss:Width="45.75" ss:Span="1"/>
159    <Column ss:Index="6" ss:StyleID="s62" ss:Width="91.5"/>
160    <Column ss:Index="9" ss:StyleID="s62" ss:Width="24.75"/>
161    <Row ss:AutoFitHeight="0" ss:Height="12.75" ss:StyleID="s63">
162     <Cell ss:StyleID="s64"><Data ss:Type="String">Raw Materials</Data></Cell>
163     <Cell ss:StyleID="s64"><Data ss:Type="String">**</Data></Cell>
164     <Cell ss:StyleID="s64"><Data ss:Type="String">a</Data></Cell>
165     <Cell ss:StyleID="s64"><Data ss:Type="String">b</Data></Cell>
166     <Cell ss:StyleID="s64"><Data ss:Type="String">c</Data></Cell>
167     <Cell ss:StyleID="s64"><Data ss:Type="String">d</Data></Cell>
168     <Cell ss:StyleID="s64"><Data ss:Type="String">e</Data></Cell>
169     <Cell ss:StyleID="s64"><Data ss:Type="String">f</Data></Cell>
170     <Cell ss:StyleID="s64"><Data ss:Type="String">g</Data></Cell>
171     <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
172     <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
173     <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
174     <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
175     <Cell ss:StyleID="s65"><Data ss:Type="String"></Data></Cell>
176    </Row>
177    <Row ss:AutoFitHeight="0">
178     <Cell ss:StyleID="s66"><Data ss:Type="String">ARA</Data></Cell>
179     <Cell ss:StyleID="s66"><Data ss:Type="String">沙轻-沙特</Data></Cell>
180     <Cell ss:StyleID="s68"><Data ss:Type="Number">5.4459999999999997</Data></Cell>
181     <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
182     <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
183     <Cell ss:StyleID="s68"><Data ss:Type="Number">5445.6</Data></Cell>
184     <Cell ss:StyleID="s68"><Data ss:Type="Number">0.17</Data></Cell>
185     <Cell ss:StyleID="s68"><Data ss:Type="Number">0</Data></Cell>
186     <Cell ss:StyleID="s66"><Data ss:Type="String">JIK</Data></Cell>
187    </Row>  
188   </Table>
189   <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
190    <Unsynced/>
191    <Print>
192     <ValidPrinterInfo/>
193     <PaperSizeIndex>9</PaperSizeIndex>
194     <HorizontalResolution>200</HorizontalResolution>
195     <VerticalResolution>200</VerticalResolution>
196    </Print>
197    <Selected/>
198    <Panes>
199     <Pane>
200      <Number>3</Number>
201      <ActiveRow>8</ActiveRow>
202      <ActiveCol>2</ActiveCol>
203     </Pane>
204    </Panes>
205    <ProtectObjects>False</ProtectObjects>
206    <ProtectScenarios>False</ProtectScenarios>
207   </WorksheetOptions>
208 </Worksheet>
209 </Workbook>
210 ';
211 WITH
212 XMLNAMESPACES(
213      DEFAULT N'urn:schemas-microsoft-com:office:spreadsheet'
214 )
215 SELECT
216      T.c.value('(Cell/Data/text())[1]', 'nvarchar(100)'),
217      T.c.value('(Cell/Data/text())[2]', 'nvarchar(100)'),
218      T.c.value('(Cell/Data/text())[3]', 'nvarchar(100)'),
219      T.c.value('(Cell/Data/text())[4]', 'nvarchar(100)'),
220      T.c.value('(Cell/Data/text())[5]', 'nvarchar(100)'),
221      T.c.value('(Cell/Data/text())[6]', 'nvarchar(100)'),
222      T.c.value('(Cell/Data/text())[7]', 'nvarchar(100)'),
223      T.c.value('(Cell/Data/text())[8]', 'nvarchar(100)'),
224      T.c.value('(Cell/Data/text())[9]', 'nvarchar(100)')
225 FROM @xml.nodes('/Workbook/Worksheet/Table/Row') T(c)  --表值方法需要有参数,呢个参数就是列名
226 ;
227 
228 
229 --动态列名
230 DECLARE
231      @sql nvarchar(max),
232      @position int
233 ;
234 SELECT
235      @sql = N'',
236      @position = 0
237 ;
238 WITH
239 XMLNAMESPACES(
240      DEFAULT N'urn:schemas-microsoft-com:office:spreadsheet'
241 )
242 SELECT
243      @position = @position + 1,
244      @sql = @sql + N',
245      ' + QUOTENAME(T.c.value('(.)[1]', 'nvarchar(100)'))
246      + N' = T.c.value(''(Cell/Data/text())[' + RTRIM(@position) + N']'', ''nvarchar(100)'')'
247 FROM @xml.nodes('/Workbook/Worksheet/Table/Row[position()=1]/Cell/Data/text()') T(c)
248 ;
249 SET @sql = N'
250 WITH
251 XMLNAMESPACES(
252      DEFAULT N''urn:schemas-microsoft-com:office:spreadsheet''
253 )
254 SELECT'
255      + STUFF(@sql, 1, 1, N'')
256      + N'
257 FROM @xml.nodes(''/Workbook/Worksheet/Table/Row[position()>1]'') T(c)
258 ';
259 print @sql;
260 EXEC sp_executesql
261      @sql,
262      N'
263           @xml xml
264      ',
265      @xml
266 ;

 

posted @ 2013-08-02 22:39 桦仔 阅读(...) 评论(...) 编辑 收藏