Get xml value from XML file
Get xml value from a xml file
Description
This code snippet will help you to extract the xml content from the xml file,
what you have to do is to specify the xml path and have to get the admin
rights, once done, execute the below code.
DECLARE @FileName varchar(255) -- path of your xml
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @XMLFileContents VARCHAR(8000)
CREATE TABLE #tempXML(ID INT IDENTITY(1,1), xmlLines VARCHAR(255))
SET @FileName = 'C:\Test.xml'
SET @ExecCmd = 'type ' + @FileName
SET @XMLFileContents = ''
INSERT INTO #tempXML
EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
SET @x = 1
WHILE @x <= @y
BEGIN
SELECT @XMLFileContents = @XMLFileContents + ISNULL(xmlLines,'') from #tempXML WHERE ID = @x
SET @x = @x + 1
END
SELECT @XMLFileContents as XMLFileContents
DROP TABLE #tempXML
you can use this when you use the bulk insert. using open xml you can specify the fields which you want to extract.
