Convert XML data into row and columns format in SQL Server

XML Shredding in SQL Server for present in table format

Present the XML data in table format as follows:

--Convert xml data into row and columns format
--- XML Shred ---

DECLARE @v_rawXML varchar(1000);
DECLARE @v_XMLinMemory int;

SET @v_rawXML =
'<Employee>
	<FirstName>RAKESH</FirstName>
	<LastName>KUMAR</LastName>
</Employee>';

EXEC sp_xml_preparedocument @v_XMLinMemory OUTPUT, @v_rawXML;

SELECT    *
FROM       OPENXML (@v_XMLinMemory, '/Employee', 2)
           with(FirstName varchar(100),
		LastName varchar(100)  )

exec sp_xml_removedocument @v_XMLinMemory;

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.