How to generate XML Using C# and How to Read XML using SQL Server 2005?

How to generate XML Using C# and How to Read XML using SQL Server 2005?

Here is the sample code for my requirement.
You can make changes for your requirement.

//XML Generation using c#



//My XML Requirement is



'<Person>

<Info Name ="Karthik" Age="25" />

<Info Name ="Arun" Age="25" />

<Info Name ="Vaduvoor Kumar" Age="25" />

</Person>'



Person - Root Element

Info - Child Element

Name,Age are Attributes.





// Using C# to create XML



XmlDocument doc = new XmlDocument();

XmlNode root = doc.CreateElement("Person"); -- root element creation

XmlElement newElem;

doc.AppendChild(root);



DataRow objR; -- DataRow



for (int i = 0; i < dt.Rows.Count; i++)

{

objR = dt.Rows[i]; -- Current Row..

newElem = doc.CreateElement("Info"); --- Child Element

doc.DocumentElement.AppendChild(newElem);



newElem.SetAttribute("Name", objR["Name"].ToString()); -- Attributes

newElem.SetAttribute("Age", objR["Age"].Tostring());



}

strRatingXML= doc.OuterXml;



// Write code to Pass this XML into Stored Procedure using appropriate code..







// Inside the Stored Procedure.

// To read xml content and put values into sampleTable

declare @PersonInfo xml



set @SiteRating =

'<Person>

<Info Name ="Karthik" Age="25" />

<Info Name ="Arun" Age="25" />

<Info Name ="Vaduvoor Kumar" Age="25" />

</Person>'









dECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT,@PersonInfo --xmlfield





INSERT INTO

sampleTable

SELECT

Name,Age, [Count] --into #temp

FROM OPENXML

(@idoc, '/SiteRating/Site',1)

WITH (Name varchar(50), Age int)



EXEC sp_xml_removedocument @idoc

No comments: