Read Data From XML File in SQL Server

Lakhangarg
Posted by in Sql Server category on for Intermediate level | Views : 15458 red flag

Read Data From XML File in SQL Server using OPENXML
Introduction
sp_xml_preparedocument - Reads the XML text provided as input (@XMLDATA) ,
the xml text and returns a handle that can be used to access the newly created internal representation of the XML document. The output of this procedure is XML handle that is
used for OPENXML command.

sp_xml_removedocument - A parsed document is stored in the internal cache of
SQL Server, so sp_xml_removedocument is used to remove the xml document.

OPENXML- OPENXML provides a rowset view over an XML document. Because
OPENXML is a rowset provider, OPENXML can be used in Transact-SQL
statements in which rowset providers such as a table, view, or the
OPENROWSET function can appear.

The last Attribute is for flag means weather it is attribute centric, element
centric etc.

Conclusion
DECLARE @xmlDoc VARCHAR(8000)
DECLARE @xmlHandle INT
BEGIN
SET @xmlDoc ='{Products}
{Product ID="1"}
{Name}Sugar{/Name}
{Price}30 rs/Kg{/Price}
{/Product}
{Product ID="2"}
{Name}Milk{/Name}
{Price}22 rs/Kg{/Price}
{/Product}
{/Products}'

EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlDoc
SELECT * FROM OPENXML (@xmlHandle, '//Product', 2) WITH
(
ID INT '@ID',
[Name] VARCHAR(20) 'Name',
Price VARCHAR(10) 'Price'
)
EXEC sp_xml_removedocument @xmlHandle
END


Output:
ID Name Price
1 Sugar 30 rs/Kg
2 Milk 22 rs/


Page copy protected against web site content infringement by Copyscape

About the Author

Lakhangarg
Full Name: Lakhan Pal
Member Level: Silver
Member Status: Member,Moderator
Member Since: 8/17/2009 12:39:46 AM
Country: India

http://lakhangarg.blogspot.com
Hello Friends Myself Lakhan Pal Garg and i am a B.Tech (IT) Graduate and having 8+Years of Exp. in Microsoft Technology. I have Write a Blog Named Free Code Snippets (http://lakhangarg.blogspot.com/) I hope you must visit my blog as your valuable feedback will motivate me to write more and improve my mistake. If you want to gain more knowledge then share it with others.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)