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

G

Guest

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
 
G

Guest

what do you want to insert?
a plain xml or a set of tables in a dataset???
Remember that a data set is no serializable

you should send DataTable
a said:
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
Click to expand...
 
G

Guest

Hi:

I'm not understanding this...I can get the SPROC to run when i insert the
xml as a string with single quotes around it (thats SPROC #2 below - see the
SET statement), so I'm thinking that the dataset does not look like a string
with single quotes on either end. Therefore I don't need to pass it in as a
dataset, but I do need to passs the xml file in as a variable.

Does that help?

Thanks,

Paul
----------------------------------------------------------------------------------

stb said:
what do you want to insert?
a plain xml or a set of tables in a dataset???
Remember that a data set is no serializable

you should send DataTable
a said:
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
Click to expand...
Click to expand...
 
Top