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.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: