Establish a stored procedure to return the data to be read to form a result set:
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 ' [email protected]+ ', single_blob) as Bulkcolumn '
EXECUTE sp_executesql @sqlCmd, N ' @T XML output ', @[email protected] 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
Read the XML file to get the saved data information