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.Exe cuteNonQuery();
(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("owners hipDocument");
string filePath = "D:\\SEC\\Progr amming\\SEC DataBots\\SEC DataBot Test
Files\\Form 4\\From QS.xml";
dsInsiderOwners .ReadXml(filePa th);
string myConnectionStr ing = "workstatio n id=AMD;packet
size=4096;integ rated security=SSPI;d ata source=AMD;pers ist security
info=False;init ial catalog=MyDatab ase";
SqlConnection sqlConnection1 = new SqlConnection(m yConnectionStri ng);
SqlCommand sqlCommand1 = new SqlCommand();
sqlCommand1.Con nection = sqlConnection1;
sqlCommand1.Com mandType = System.Data.Com mandType.Stored Procedure;
sqlCommand1.Com mandText = "_sp_Insert_For m_004_XML_templ ate_07";
sqlCommand1.Par ameters.Add(new
System.Data.Sql Client.SqlParam eter("@Form_004 ", System.Data.Sql DbType.NText));
sqlCommand1.Par ameters[0].Value = dsInsiderOwners ;
sqlConnection1. Open();
sqlCommand1.Exe cuteNonQuery();
sqlCommand1.Con nection.Close() ;
============ SPROC #1 - accepts xml as a variable =============== ===
CREATE PROC _sp_Insert_Form _004_XML_templa te_07 (@Form_004 nText)
AS
DECLARE @iDoc int--............... ............... .............va riable to
hold parsed xml stream
EXEC sp_xml_prepared ocument @iDoc OUTPUT, @Form_004--.....create parsed xml
stream
SELECT * INTO Form_004_A--............... ............... ......To Insert Into
A NEWLY Created Table
FROM OPENXML(@iDoc, 'ownershipDocum ent/issuer',3)
WITH (issuerCik char(10) , issuerName char(40) , issuerTradingSy mbol
char(10))
EXEC sp_xml_removedo cument @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"?>
<ownershipDocum ent>
<schemaVersion> X0202</schemaVersion>
<documentType>4 </documentType>
<periodOfReport >2005-04-29</periodOfReport>
<notSubjectToSe ction16>0</notSubjectToSec tion16>
<issuer>
<issuerCik>0000 796343</issuerCik>
<issuerName>ADO BE SYSTEMS INC</issuerName>
<issuerTradingS ymbol>ADBE</issuerTradingSy mbol>
</issuer>
</ownershipDocume nt>'
EXEC sp_xml_prepared ocument @iDoc OUTPUT, @XMLDoc
SELECT * INTO Form_004_A
FROM OPENXML(@iDoc, 'ownershipDocum ent/issuer',3)
WITH (issuerCik char(10) , issuerName char(40) , issuerTradingSy mbol
char(10))
EXEC sp_xml_removedo cument @iDoc
SELECT * FROM Form_004_A
GO
