Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / Visual Studio.NET / General / May 2005

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
a - 26 May 2005 16:11 GMT
how to send an xml dataset to a Stored Procedure in mssql

I've been trying to do this manually...I'm wondering how to do this (if it
can be done) in Visual Studio
----------------------------------------------------------------------------------
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
Ravi Shankar - 27 May 2005 10:01 GMT
Replace: sqlCommand1.Parameters[0].Value = dsInsiderOwners;
with: sqlCommand1.Parameters[0].Value = dsInsiderOwners.GetXml();

> how to send an xml dataset to a Stored Procedure in mssql
>
[quoted text clipped - 103 lines]
>
> GO
a - 27 May 2005 21:56 GMT
Ravi:

Thank you for that correction.  That appears to help the code run better.  I
may have had some issues with my xml test documents also.

Paul
PS Are you any relation to the musician Ravi Shankar? :-)
=========================================

> Replace: sqlCommand1.Parameters[0].Value = dsInsiderOwners;
> with: sqlCommand1.Parameters[0].Value = dsInsiderOwners.GetXml();
[quoted text clipped - 106 lines]
> >
> > GO

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.