匯入xml檔案到SQL Server 2005

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