dotnet xml: how to send an xml dataset to a Stored Procedure in mssql -- dotnet xml

how to send an xml dataset to a Stored Procedure in mssql

a
5/26/2005 7:30:05 AM

dotnet xml: how to send an xml dataset to a Stored Procedure in mssql

I have an xml file that I read into a dataset.
and I'm trying to use a stored procedure (see SPROC #1 below)
that inserts this xml dataset into an sql table via OpenXML.
Nothing gets inserted into the sql table except nulls,
so I think that perhaps I'm not actually sending the (xml)
dataset to the SPROC.

The code below fails on this line: sqlCommand1.ExecuteNonQuery();

(The SPROC works fine when I run it in Query Analyzer
and SET the xml file directly as a string. See the SPROC #2 below)

What do I need to do to send the xml datset to the SPROC?

Thank you.

Paul

============ C# ===============

DataSet dsInsiderOwners = new DataSet("ownershipDocument");
string filePath = "D:\\SEC\\Programming\\SEC DataBots\\SEC DataBot Test
Files\\Form 4\\From QS.xml";

dsInsiderOwners.ReadXml(filePath);

string myConnectionString = "workstation id=AMD;packet
size=4096;integrated security=SSPI;data source=AMD;persist security
info=False;initial catalog=MyDatabase";
SqlConnection sqlConnection1 = new SqlConnection(myConnectionString);

SqlCommand sqlCommand1 = new SqlCommand();
sqlCommand1.Connection = sqlConnection1;
sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand1.CommandText = "_sp_Insert_Form_004_XML_template_07";
sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Form_004", System.Data.SqlDbType.NText));
sqlCommand1.Parameters[0].Value = dsInsiderOwners;

sqlConnection1.Open();
sqlCommand1.ExecuteNonQuery();
sqlCommand1.Connection.Close();

============ SPROC #1 - accepts xml as a variable ==================

CREATE PROC _sp_Insert_Form_004_XML_template_07 (@Form_004 nText)

AS

DECLARE @iDoc int--...........................................variable to
hold parsed xml stream

EXEC sp_xml_preparedocument @iDoc OUTPUT, @Form_004--.....create parsed xml
stream

SELECT * INTO Form_004_A--....................................To Insert Into
A NEWLY Created Table

FROM OPENXML(@iDoc, 'ownershipDocument/issuer',3)

WITH (issuerCik char(10) , issuerName char(40) , issuerTradingSymbol
char(10))

EXEC sp_xml_removedocument @iDoc

GO

============ SPROC #2 - for QueryAnalyzer (xml set as a string)
==================

DECLARE @iDoc int

DROP TABLE Form_004_A

DECLARE @XMLDoc varchar(8000)
SET @XMLDoc = '<?xml version="1.0"?>
<ownershipDocument>
<schemaVersion>X0202</schemaVersion>
<documentType>4</documentType>
<periodOfReport>2005-04-29</periodOfReport>
<notSubjectToSection16>0</notSubjectToSection16>
<issuer>
<issuerCik>0000796343</issuerCik>
<issuerName>ADOBE SYSTEMS INC</issuerName>
<issuerTradingSymbol>ADBE</issuerTradingSymbol>
</issuer>
</ownershipDocument>'

EXEC sp_xml_preparedocument @iDoc OUTPUT, @XMLDoc

SELECT * INTO Form_004_A

FROM OPENXML(@iDoc, 'ownershipDocument/issuer',3)

WITH (issuerCik char(10) , issuerName char(40) , issuerTradingSymbol
char(10))

EXEC sp_xml_removedocument @iDoc

SELECT * FROM Form_004_A

GO

Re: how to send an xml dataset to a Stored Procedure in mssql

Prasad Dannani
7/15/2005 12:00:00 AM

Hi,

Use IO Classes and Send the XML Content to the value property of the
parameter.

Regards,

Prasad Dannani.

[quoted text, click to view]