Monday, January 16, 2012

Multiple Records Insertion using XML

// As we are preparing XML Document pragmatically based on the available Data Table.

public string WriteXmlIdentityInformation(DataTable dataTable)
{
string returnXML = string.Empty;
XmlDocument doc = new XmlDocument();
//XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
//doc.AppendChild(dec);
// Create the root element
XmlElement root = doc.CreateElement("DocumentElement");
doc.AppendChild(root);
foreach (DataRow dr in dataTable.Rows)
{
XmlElement myElement = doc.CreateElement("myDt");
myElement.SetAttribute("Field1", dr[0].ToString());
myElement.SetAttribute("Field2", dr[1].ToString());
myElement.SetAttribute("Field3", dr[2].ToString());
myElement.SetAttribute("Field4", dr[3].ToString());
myElement.SetAttribute("Field5", dr[4].ToString());
root.AppendChild(myElement);
returnXML= doc.OuterXml;
}
return returnXML;
}

//Sending XML document through DB Method..

if (xmlIdentityInformation != string.Empty) cmdUpdate.Parameters.AddWithValue("@PersonInformationDomain", xmlIdentityInformation);
else
cmdUpdate.Parameters.AddWithValue("@PersonInformationDomain", DBNull.Value);


//Inserting records in the DB Table.

DECLARE @intPersonInfDomainDocHandle int

//Create an internal representation of the document.

EXEC sp_xml_preparedocument @intPersonInfDomainDocHandle OUTPUT, @PersonInformationDomain

INSERT INTO Directory.Table( field1, field2,field3,field4,field5)
SELECT field1, field2,field3,field4,field5
FROM OPENXML(@intPersonInfDomainDocHandle,'/DocumentElement/myDt')
WITH
(
field1 int, field2 INT,field3 INT,field4 INT, field5 int
)
Where some condition

-- Remove the internal representation.
EXEC sp_xml_removedocument @intPersonInfDomainDocHandle

Refeneces..

NO 1
NO 2
NO 3
NO 4

No comments:

Post a Comment