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 2 2608
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: 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
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: 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:
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ryan |
last post by:
I have two similar stored procedures which I'm running. One runs and
one doesn't. I can run both with no problems in SQL Enterprise (7.0
standard) and have checked the permissions and am happy with...
|
by: a |
last post by:
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...
|
by: Mike P |
last post by:
Is it possible to return a dataset from a stored procedure, or would you
need to write the SQL in your .cs file to return the dataset?
Any assistance would be really appreciated.
Cheers,
...
|
by: Leon |
last post by:
I have six textbox controls on my webform that allows the user to enter any
numbers from 1 to 25 in any order. However, I would like to sort those
numbers from least to greatest before sending them...
|
by: JIM.H. |
last post by:
Hello,
Can I send a dataset as a parameter into stored procedure and import data to
a table in the stored procedure?
Thanks,
Jim.
|
by: Dennis |
last post by:
Hi
I'm trying to alter my stored procedure to take a parameter for the
Database Name, but as usual the syntax is killing me.
Thanks for any help
Dennis
...
|
by: aCe |
last post by:
hi all,
i need to convert these simple PHP code into stored procedure :
<?php
$result = mssql_query( "SELECT whid, whcode FROM warehouse" );
while( $wh = mssql_fetch_object( $result ) )
{...
|
by: ckauvar |
last post by:
The PHP below calls a stored procedure in a MSSQL database when I am
using SQL in a Windows environment. I've recently switched to a UNIX
environment and am now using ODBC (via FreeTDS) to connect...
|
by: Plater |
last post by:
So this is kind of an odd request.
I want to be able to return a small "hardcoded" dataset from a stored procedure.
Is that even possible?
I want for example a "GetAvailableTypes" store procedure...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |