excel格式的XML文件导入SQL server的问题
小弟有多个XML文件，想导入到SQL Server中，每个文件生成一个表。
用的如下代码，
DECLARE @idoc int;
DECLARE @doc xml;
DECLARE @a varchar(50);
SELECT @doc=bulkcolumn FROM OPENROWSET(
BULK 'D:test\test5.xml',
SINGLE_BLOB) AS x
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc
SELECT * into #temp1 FROM OPENXML (@Idoc, '/Workbook/Worksheet/Table/Row/Cell',2)
WITH (
[Data] varchar(20)此处应该如何编写数据格式？
)
select * from #temp1
drop table #temp1
另，循环怎么编写？感激不尽！！！
XML文件格式如下
<?xml version="1.0" encoding="GBK"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
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">
<Worksheet ss:Name="Table1">
<Table>
<Column ss:Index="1" ss:AutoFitWidth="0" ss:Width="110"/>
<Row>
<Cell><Data ss:Type="String">ID</Data></Cell>
<Cell><Data ss:Type="String">time</Data></Cell>
<Cell><Data ss:Type="String">usrname</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">154</Data></Cell>
<Cell><Data ss:Type="String">2012-11-06 00:00:30</Data></Cell>
<Cell><Data ss:Type="String">Jack</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
[解决办法]
declare @idoc int
declare @doc xml =
'<?xml version="1.0" encoding="GBK"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
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">
<Worksheet ss:Name="Table1">
<Table>
<Column ss:Index="1" ss:AutoFitWidth="0" ss:Width="110"/>
<Row>
<Cell><Data ss:Type="String">ID</Data></Cell>
<Cell><Data ss:Type="String">time</Data></Cell>
<Cell><Data ss:Type="String">usrname</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">154</Data></Cell>
<Cell><Data ss:Type="String">2012-11-06 00:00:30</Data></Cell>
<Cell><Data ss:Type="String">Jack</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>'
-- 1.openxml
exec sp_xml_preparedocument @idoc output, @doc, N'<xlsx xmlns:x="urn:schemas-microsoft-com:office:spreadsheet"/>'
select * from openxml(@idoc, '//x:Row[position()>1]') with -- 需要标题行去掉[position()>1]
(
id int 'x:Cell[1]/x:Data', -- 带标题行id/time类型改为varchar(n)
time datetime 'x:Cell[2]/x:Data',
usrname varchar(20) 'x:Cell[3]/x:Data'
)
/*
id time usrname
---------- ----------------------- ----------
154 2012-11-06 00:00:30.000 Jack
*/
exec sp_xml_removedocument @idoc
-- 2.nodes()/value()
;with xmlnamespaces (default 'urn:schemas-microsoft-com:office:spreadsheet')
select
n.x.value('Cell[1]','int') id,
n.x.value('Cell[2]','datetime') time,
n.x.value('Cell[3]','varchar(20)') usrname
from
@doc.nodes('//Row[position()>1]') n(x)
/*
id time usrname
---------- ----------------------- ----------
154 2012-11-06 00:00:30.000 Jack
*/
