XMLData in SqlServer /ASp.Net Xml File insert Via Sp

create table XmlData(CustID int,EmpID int)

Declare @hdoc int
Declare @doc Varchar(1000)
set @doc='
<?xml version="1.0" encoding="utf-8"?>
<XmlData>
<CustID>105</CustID>
<EmpID>105</EmpID>
</XmlData>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
INSERT INTO XmlData( CustID, EmpID)
SELECT CustID, EmpID FROM Openxml( @hdoc, '/XmlData', 3) WITH ( CustID int,EmpID int)
exec sp_xml_removedocument @hdoc

select * from XmlData

--Procedure method insert Xml Value to sql server

create Proc InsertXmlDocument(@documents varchar(max))
With Encryption
as
begin
Declare @doc Varchar(1000)
set @doc=@documents
Declare @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
INSERT INTO XmlData( CustID, EmpID)
SELECT CustID, EmpID FROM Openxml( @hdoc, '/XmlData', 3) WITH ( CustID int,EmpID int)
exec sp_xml_removedocument @hdoc
End

Exec InsertXmlDocument '
<?xml version="1.0" encoding="utf-8"?>
<XmlData>
<CustID>999</CustID>
<EmpID>888</EmpID>
</XmlData>'

-- Asp.net Using Insert Proc Via Xml File

 string xml = File.ReadAllText("C:\\Test.xml");
            string strQry = "exec InsertXmlDocument '" + xml + "'";
            DB.Instance.GetDataSet(strQry);

Browse More Articles

Tweet

Written by Test

We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.