笔记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 ;
