读取 xml 文件 获取其中保存的数据信息

 

建立一个存储过程来返回要读取的数据形成结果集：

CREATE PROC dbo.getValuesFromXmlByPath
@fileName NVARCHAR(128)
as
DECLARE @T XML,
@idoc INT,
@existFlag int
EXEC xp_fileexist @fileName,@existFlag OUTPUT
IF @existFlag=1
BEGIN

DECLARE @xmltab TABLE
(
[Alias] VARCHAR(15),
[ServerName] VARCHAR(15),
[DatabaseName] VARCHAR(63)
)

DECLARE @sqlCmd NVARCHAR(max)='SELECT @T=BULKCOLUMN FROM OPENROWSET(BULK '''+@fileName+''',SINGLE_BLOB) AS BULKCOLUMN'
EXECUTE SP_EXECUTESQL @sqlCmd,N'@T XML OUTPUT',@T=@T OUTPUT
EXECUTE SP_XML_PREPAREDOCUMENT @idoc OUTPUT,@T

INSERT INTO @xmltab
( Alias, ServerName, DatabaseName )
SELECT [Alias],
[location],
[name]
FROM OPENXML(@idoc,'/databases/database',1)
with(
[alias] VARCHAR(15),
[location] VARCHAR(15),
[name] VARCHAR(63)
)

EXECUTE SP_XML_REMOVEDOCUMENT @idoc
SELECT * FROM @xmltab

END