So some times we want to read xml file in sql server. I am passing list of details to sql server using xml file format. It reduce several db calls when we passing list of data.
Suppose we have List of Students. In List of Student we have to loop it and fire db calls to save data. To overcome this I create xml of List of Student and Pass it to sql server with one db call and then in my stored procedure I will handle those data as follow.
DECLARE @Xml XML DECLARE @HDoc INT SET @Xml = '<ArrayOfStudent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Student> <Id>1</Id> <FirstName>jeevan</FirstName> <LastName>JLast name</LastName> </Student> <Student> <Id>2</Id> <FirstName>john</FirstName> <LastName>johnLast name</LastName> </Student> </ArrayOfStudent>' EXECUTE SP_XML_PREPAREDOCUMENT @HDoc OUTPUT,@Xml SELECT [Id], [FirstName], [LastName] INTO #Temp FROM OPENXML(@HDoc, '/ArrayOfStudent/Student', 1) WITH ( [Id] INT 'Id', [FirstName] VARCHAR(400) 'FirstName', [LastName] VARCHAR(400) 'LastName' ) EXECUTE SP_XML_REMOVEDOCUMENT @HDoc SELECT * FROM #Temp