One is best way you can pass xml file as ntext datatype parameter in stored proc and than you can read that xml within stored proc using sp_xml_preparedocument
eg
Create proc spname
(
@LineData nText--grid data
)
AS
Begin
DECLARE @hDoc1 int
exec sp_xml_preparedocument @hDoc1 OUTPUT,@LineData
if exists(select * FROM OPENXML (@hdoc1, '/NewDataSet/LineItmData',1))
SELECT InsuranceAddressId,PrimaryNo,InsuranceId,Address1,Address2,Phone1,Phone2,Email,Fax,Fax2,City,StateID,Zip,Notes,Disabled,RecordStatus,CreatedBy,ModifiedBy
FROM OPENXML (@hdoc1, '/NewDataSet/LineItmData',1)
WITH (
InsuranceAddressId int) As tblname
-- you can insert that value in any table or what you wnat to do within sp.
End
Or also one more option, you can get directly xml in stored proc by using file path
eg
CREATE PROC [dbo].[USP_READXMLFILE]
AS
/*
EXEC [USP_READXMLFILE]
*/
BEGIN
SET NOCOUNT ON
DECLARE @HANDLE INT
DECLARE @SQUERY VARCHAR(1000)
DECLARE @XMLDOC XML
SET @XMLDOC = (SELECT * FROM OPENROWSET
(BULK 'd:\TestXML.xml', SINGLE_CLOB) AS XMLDATA)
SELECT @XMLDOC
EXEC SP_XML_PREPAREDOCUMENT @HANDLE OUTPUT,@XMLDOC
SELECT * FROM OPENXML(@HANDLE, '/XMLData/tblEmp', 2)
WITH (name VARCHAR(50),designation VARCHAR(50))
END
Regards & thanks
Arvind kumar
Visit--blog.akumars.esoftera.in