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" wrote:
[color=blue]
> 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" wrote:
>[color=green]
> >
> > 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--...........................................variabl e 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[/color][/color]
