27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @x xml
SET @x='
<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>Joe</name></row>
<row id="3" />
</Root>
'
SELECT T.c.value('@id','int') as id,
T.c.value('name[1]','nvarchar(10)') as NAME
FROM @x.nodes('/Root/row[name]') T(c)
go
id NAME
----------- ----------
1 Larry
2 Joe
(2 行受影响)
导入xml文件到SQL Server 2005
===========================================================
作者: kitesky(http://kitesky.itpub.net)
发表于:2009.07.27 12:09
分类: SQL Server
出处:http://kitesky.itpub.net/post/909/488355
---------------------------------------------------------------
SQLServer2005分解并导入xml文件
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