Question

xml文件导入sql数据库

<?xml version="1.0" encoding="utf-8"?>
<dataset>
<Army entId="10500000" armyName="新兵" level="0" armyDesc="" armyType="" iconPath="" attack="0" defence="0" agile="0" hp="0" attackType="" defenceType="" moveRange="0" mobility="0" convey="0" hitRate="0" dodgeRate="0" criticalAtkRate="0" caromAtkRate="0" counterAtkRate="0" attackRange="0" consumeFood="0" />

</dataset> 数据是这样的 Navicat可以直接导入但是数据太多要写个小程序怎么做？

Answer 1

SQLServer2005分解并导入xml文件[@more@]
1. 一次性导入：
DECLARE @idoc int;
DECLARE @doc xml;
SELECT @doc=BulkColumn FROM OPENROWSET(BULK N'E:MStarIndustryCodes.xml', SINGLE_BLOB) AS x
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT * into tmp_tab FROM OPENXML (@idoc, '/Root/Record'/',2)
WITH
(
IndustryCode varchar(10)
,IndustryGlobalId varchar(10)
,IndustryName varchar(100)
,SectorCode varchar(10)
,SectorGlobalId varchar(10)
,SectorName varchar(100)
,SuperSectorCode varchar(10)
,SuperSectorName varchar(100)
,GroupCode varchar(10)
,GroupName varchar(100)
,CountryId varchar(3)
)
EXEC sp_xml_removedocument @idoc
select * from tmp_tab
2. 先导入到表中varchar(MAX)列，然后再用OPENXML解析，读出。
-- 使用SINGLE_CLOB参数，tmp_raw中字段为varcahr(MAX)类型
SELECT * into tmp_raw FROM OPENROWSET(BULK N'E:MStarIndustryCodes.xml', SINGLE_CLOB) AS x
DECLARE @idoc int;
DECLARE @doc xml;
select @doc = BulkColumn from tmp_raw
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT top 10 * FROM OPENXML (@idoc, '/Root/Record', 1)
WITH
(
IndustryCode varchar(10)
,IndustryGlobalId varchar(10)
,IndustryName varchar(100)
,SectorCode varchar(10)
,SectorGlobalId varchar(10)
,SectorName varchar(100)
,SuperSectorCode varchar(10)
,SuperSectorName varchar(100)
,GroupCode varchar(10)
,GroupName varchar(100)
,CountryId varchar(3)
)
EXEC sp_xml_removedocument @idoc