DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(max)
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileName = 'D:\MyShopData\MobileOperatorIdentify.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
SET @x = 0
WHILE @x <> @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK
= @x
END
SELECT @FileContents as FileContents
DROP TABLE #tempXML
print @FileContents
DECLARE @iDoc INT
EXEC sp_xml_preparedocument @iDoc OUTPUT, @FileContents
SELECT PrefixId, OperatorId, PrefixNumber
FROM OPENXML(@iDoc,'/DocumentElement/dtMobileOperatorPrefix',2)
With(
PrefixId varchar(10) 'PrefixId',
OperatorId varchar(10) 'OperatorId',
PrefixNumber varchar(10) 'PrefixNumber'
)
