how to send an xml dataset to a Stored Procedure i

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

how to send an xml dataset to a Stored Procedure i

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2005-05-26 : 17:34:17

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

kloepper
Yak Posting Veteran

77 Posts

Posted - 2005-05-27 : 00:02:02

OK...I didn't validate the test document...it turns out to have an error in it.
The code I've been using is actually ok--except that it needs to also validate the files.

Paul

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-27 : 00:59:26

string filePath = @"D:\SEC\Programming\SEC DataBots\SEC DataBot Test
Files\Form 4\From QS.xml"; much nicer

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede™

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources