reading xml in sql server

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

xml reading

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s