.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Imran Ghani
Post New Web Links

Problem when Inserting xml data into a table using OpenXml

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
 
Hi, i stuck with a problemam trying to insert data from xml file to sql server.i made query like thisSET NOCOUNT ON DECLARE @hDoc INT EXEC sp_xml_preparedocument @hDoc OUTPUT, '<NewDataSet>  <xs:schema id='NewDataSet' xmlns='' xmlns:xs='http://www.w3.org/2001/XMLSchema' xmlns:msdata='urn:schemas-microsoft-com:xml-msdata'>  <xs:element name='Job'>     <xs:complexType>      <xs:sequence>        <xs:element name='BillToNo' type='xs:string' minOccurs='0' /> <xs:element name='Colors' type='xs:string' minOccurs='0' /> <xs:element name='CompanyNo' type='xs:string' minOccurs='0' /> <xs:element name='CsrCode' type='xs:string' minOccurs='0' /> <xs:element name='CsrName' type='xs:string' minOccurs='0' /> <xs:element name='CustomerPoNo' type='xs:string' minOccurs='0' /> <xs:element name='DivisionOriginal' type='xs:string' minOccurs='0' /> <xs:element name='EstimateNo' type='xs:string' minOccurs='0' /> <xs:element name='FinalDeliveryDate' type='xs:string' minOccurs='0' /> <xs:element name


View Complete Post


More Related Resource Links

Problem in Stored procedure when inserting data through openxml

  

I have stored proc where i insert some value from openxml but it is not inserting that xml data. Below is my stored proc.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_MKTG_SERVICE_INSERT_NEW]
@ID AS INT,
@STATUS AS NVARCHAR(20),
@USERNAME AS NVARCHAR(50),
@DATEOFVISIT AS DATETIME,
@SUMMARY AS NVARCHAR(500),
@HOMESERVICE AS NVARCHAR(5),
@EXPIRYDATE AS DATETIME,
@SERVICELIST XML
AS
 SET NOCOUNT ON

BEGIN TRAN
IF EXISTS(SELECT * FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID)
BEGIN
	DELETE FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID
END

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
		DECLARE @IDOC INT
		EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT,@SERVICELIST
		INSERT INTO NEW_DTL_SERVICE
		(PARTNERID,[SERVICENAME],[TYPE],DISCOUNT,COMMISSION,CONDITION)
		SELECT @ID,SERVICENAME,TYPE,DISCOUNT,COMMISSION,CONDITION
		FROM OPENXML(@IDOC,'/NEWDATASET/TABLE1',2)
		WITH (SERVICENAME NVARCHAR(50),TYPE NVARCHAR(50),DISCOUNT NVARCHAR(50),COMMISSION NVARCHAR(50),CONDITION NVARCHAR(500))
		EXEC SP_XML_REMOVEDOCUMENT @IDOC
IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
EXEC TIEDUPPARTNER @STATUS,@ID

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
COMMIT TRAN


and following is my xml which i generate to insert.

How to use OPENXML to load XML data into existing SQL Table?

  
I am a newbie to OPENXML. But I am trying to load a .XML file into a SQL table that I created for this. I do not receive any errors with this code, but it doesn't insert any records either. This is the table I created in 2008 SQL Server: CREATE TABLE HOMEROOM( HOMEROOM_TEACHER INT, HOMEROOM_NUMBER INT, ENTITY_ID INT) And this is the T-SQL code I am trying to execute: DECLARE @idoc int DECLARE @xmlDocument varchar(MAX) DECLARE @Status INT SET @xmlDocument =' <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"> <s:Schema id="RowsetSchema"> <s:ElementType name="row" content="eltOnly"> <s:AttributeType name="c0" rs:name="HOMEROOM-TEACHER" rs:number="1" rs:nullable="true"> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" /> </s:AttributeType> <s:AttributeType name="c1" rs:name="HOMEROOM-NUMBER" rs:number="2"> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10" rs:maybenull="false" /> </s:AttributeType> <s:Attri

inserting an array of data into a database table

  
im looking to insert an array of information into a database tableessentially what id like to do is something like this:            objCmd = New OleDb.OleDbCommand("INSERT INTO pluINFO VALUES *", objConn)            For j = 0 To 265                objCmd.Parameters.Add( info(j) )            Next this code gives me a syntax error for the insert statement is there anyway i can set the insert statement that would allow me to add each element of the array(looping through rather than hardcoding several hundred entries) into the table?any input appreciatedThanks

Problem with Conversion of numeric data from sql table to csv

  
I have a numeric value which is of data type Numeric(38,12) in sql server 2000. Now loading to csv what happens is 2000--data looks like 115,834,000.00 when loaded to csv ---115834000--this is how it looks. but I want it to look like as it is in the database. It would be of great help to me, if anybody can tell me the work around. Thanks, PreenSheen

Problem Facing While Inserting records into table which os dynamicaly created

  
Hi,I have created table into database dynamicaly,while inserting records into that dynamic table i faced problem.The name "STD000001" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. this error occured.I created dynamic table by ,declare @STUDENT_Table varchar(50)set @STUDENT_Table = [dbo].[GetStudentTable](@SchoolID)declare @cmd varchar(5000)Set @cmd = 'Create Table  ' + @STUDENT_Table + '(   [student_id] [varchar](10)NOT NULL,    [school_id] [varchar](50)NULL,    [class1] [varchar](50)NULL,    [division1] [varchar](50)NULL,    [educalis_id] [varchar](16)NULL,    [roll_no] [varchar](10)NULL,    [first_name] [varchar](50)NULL,    [middle_name] [varchar](50)NULL,    [last_name] [varchar](50)NULL,    [gender] [varchar](6)NULL,    [father_name] [varchar](50)NULL,    [mother_name] [varchar](50)NULL,    [father_office_address] [varchar](200)NULL,    [mother_office_address] [varchar](200)NULL,    [home_address] [varchar](200)NULL,    [father_mobile_no] [varchar](10)NULL,    [mother_mobile_no] [varchar](10)NU

inserting one table data to another after filtering nulls and duplicates

  
hi i have tbl_ip i want to filter nulls and duplicate to my tbl_ipfilter can u suggest me a stored procedure for this. data of tbl_ip is like below . ip 27.24.83.105 NULL NULL NULL 92.20.83.203 92.20.83.203 67.21.83.315

inserting data to Users Table in ASPNETDB from Create User Wizard

  

When a user starts a membership using the Create User wizard supplied by the aspnetdb profile service, I need to add a field to a table in aspnetdb and upon clicking the ContinueButtonClick insert some data.  The field I'll add is "SubscId".  My first question is what table is best for this - Users or Membership?

Secondly, what is the best way to insert new data for this scenario?  code behind?  markup?  Can someone provide basic syntax of insert code in code behind for this scenario?


Inserting and querying data on the same table

  

I need to implement a logic where in I insert or update a table in a loop.If the record is inserted then I need to update the table.To check this I have given IF NOT EXISTS statement with a query. But IF NOT EXISTS always returns true, so many records are getting inserted and update always fails

How do I resove this problem


Inserting 15 minute Interval Data in a table

  

What is the most efficient way of creating a 15 minute increment table. This table needs to continue to increment with time so its not a static table. While loop? how will that affect the performance, if I have to add dates starting 2005 till today and then basically after that with every hour a value needs to be added.. How can I go about doing that?

I am using SQL 2008

 Thanks -Sarah


Problem inserting ID value from one SQL table to another using CreateUserWizard

  

Hi,

I am designing a registration form where I use CreateUserWizard. I am inserting data into two different tables while use is created. First table is Categories (columns: CustomerID, CatCode) where I insert CatCodes based on selections made in CheckBoxList and second table is Customers (columns: CustomerID, FName, LName, Email) where I register personal details (first, last name, email etc). CustomerID is generated automatically when client registers.

What I want to do is to connect Categories and Customers tables so when client selects CatCodes during registration and selected values are written into the Categories table CatCode column, appropriate CustomerID to be written there as well (in CustomerID column which is a ForeignKey from Cusomers table).

For example: Client is registered, his CustomerID (1001) is created during registration. He also selected three categories (1,2,3) which are written into the Categories table. Now as I have CustomerID in Categories Table as well (ForeignKey from Customer Table CustomerD column), I want generated CustomerID to show up in Categories table so i could later identify what catgories did client 1001 selected.

Category codes (CatCode) is inserted using code behind file in C#:

 protected void CreateUserWizard1_CreatedUser(obje

Insert XML data into table problem

  

For the life of me I cannot figure out what's wrong with my SP. I'm receiving an XML document with a bunch of invoices like

<Invoices><Invoice><A><B><C></Invoice><Invoice><A><B><C></Invoice></Invoices> 

And I need to write each invoice as a record in SQL Server 2005

I'm getting the following error

Msg 170, Level 15, State 1, Procedure pcdAddInvoices, Line 39
Line 39: Incorrect syntax near '.'.

 

CREATE PROCEDURE pcdAddInvoices
 -- Add the parameters for the stored procedure here

 @msg xml
 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 INSERT INTO [Promacoin].[dbo].[Tblinvoices_DAX] (ObjectID,ActivityID,ResourceID,ContractID,Document
           ,_LineNo,ContractorNO,Description,Qty,AmntTotal,DateInvoice,ProdWeek,ProdYear,Employee
           ,DocType,Comp_ERP_id,ContractorName,Eenheid)
 SELECT M.Invoice.value('/ObjectID','NVARCHAR(16)') AS ObjectID,
   M.Invoice.value(

Inserting a Data Table into a SQL Table

  

Is there an easier way to insert the values contained in a data table generated in ASP into a SQL table without having to loop through all the rows in the data table and insert individually into the SQL table?

 

thanks for any help!


problem with data list when data is retrieved from table???

  

hi,

I have a datalist binded to an object data source which has a method

GetLanguageByPersonId. 

 

In the  data list Item template, I have put  a div with a heading that will not show if there is no language by that personId  in the database table.

If there  is data them problem is the datalist repeates the heading for each language instead of showing  just the heading above and the row of languages below. I have included all my code below if somebody could advise what is wrong. thank you.

 

Method in DAL

SELECT        PersonId, LanguageId,
 (SELECT LanguageName FROM Language WHERE (LanguageId = PersonLanguage.LanguageId)) AS LanguageName
FROM            PersonLanguage
WHERE        (PersonId = @PersonId)

 

 


 <asp:DataList ID="DataList3" runat="server" DataKeyField="ModelId"
        DataSourceID="ObjectDataSource4" RepeatDirection="Horizontal" >
        <ItemTemplate>
        <div id="Div03"&n

Problem filling labels with table data outside datalist using single call to db

  

On a single page, I'd like to fill 2 hyperlink controls as well as a datalist control with table data using one call to the database. The page looks like this:

<div id="PageContent">    
    <h2>
      Districts</h2>
    <p>
      Please select the district you wish to view below.</p>
    <p>
      <asp:DropDownList ID="ddlDistrict" runat="server" CssClass="textbox" 
        DataTextField="DistrictName" DataValueField="DistrictId" AutoPostBack="True" 
        onselectedindexchanged="ddlDistrict_SelectedIndexChanged">
      </asp:DropDownList>
    </p>    
    <p><asp:label ID="lblDistrictAbbrev" runat="server" Text="District Abbreviation: " />
      <asp:hyperlink ID="hyperlinkDistrictName" runat="server" 
        Text='' NavigateUrl="~/Contact/NationalCouncil.aspx" 
        Font-Bold="True" />
    </p>
    <p><asp:label ID="lblDistrictCouncillor" runat="server" Text="District Councillor: " />
      <asp:hyperlink ID="hyperlinkCouncillor" runat="server" 
        Text='' NavigateUrl="~/Contact/NationalCouncil.a

Problem inserting a null value into date column in SQL table

  

Hi all,

I am having a hard time inserting a null date into a SQL table.  Please be aware that I am using the Microsoft Application Data Blocks and not pure ADO.net.  I find a million examples of how to do this with ADO.net but cannot find anything about this using the MS Data Application Blocks.

Here is my VB.net code:

Save(Session("ref_no"),  txtdischarge_date.Text) 

Sub Save(ByVal ref_no As Integer, discharge_date As Date)

        SqlHelper.ExecuteNonQuery(connString, ref_no, discharge_date), "MyTableInsert")

End Sub

The textbox txtdischarge_date can either be a valid date or blank.  If it contains a valid date then the insert works properly.  But if the textbox  is blank, then I want to insert a null value into my date column.  I cannot get this to work.  I get this error when I run this and txtdischarge_date is blank:

Conversion from string "" to type 'Date' is not valid

Here is the code for my stored procedure, MyTableInsert:

ALTER Procedure MyTableInsert
   (@ref_no int,
    @discharge_date datetime = NULL)

SQL Update and Insert: Inserting a whole data table into another table

  

Hello,

If I create a data tabe in C# like so:

datatable t = new DataTable;

DataColum C1 = new DataColum;//ID column

t.Columns.Add(C1);

//the same with column C2

Then I fill up the table with some data.

Then the goal is to either insert this data or update data in an SQLtable in SQL. If the data is inserted, the ID column in t would be empty, if updated, it would not be. So in the insertion case, the sql query needs to make sure that unique IDs are assigned during insertion, in the update case, it just has to look for the right IDs.

How to go about this?

Asking because I am only familiar with updating and inserting single lines into a table. One could loop in C# over the datatable t of course and insert or update line by line, but this would probably be slow. Is there a way to write an SQL query like this somehow:

Insert Into SQLTable Values (@t)

?

And Update like this:

Update SQLTable SET ... Where C1 = @t.C1

Or something like that.

I know that SQL server can create automatic update and insert queries and that you can then type things in C# like

Adapter.Update(t);

But, this has proven to be super slow on our server, for unknown reasons. I have asked on this forum and got some answers from experts, but was not able to srot out the problem.

How can we write

Gridview Header & table data alignment problem

  

Hi,


Newbie to ASP.net. Programming in C#.

I'm querying a SQL database and displaying the results in a gridview.

The results can be many records so I need to provide the facility to scroll. To display the headers when scroling I have implemented ( in my asp code) the headers in another table and the data is in another table. I see now I cannot align the column headers with the table data.

Appreciate any help to fix this issue.

If this method is not a good one to display headers with data please suggest another method.( Some example code guidance would really help)


Here is my ASP code.

<div id="divProjectTableHeaderFullDetail" runat="server" style="background-color: #993300;
            height: 40px; width: 770px; margin: 0;  padding: 0 ; " >
            <table style="font-family: Arial; font-size: 10pt; color: white;" cellspacing="0"
                cellpadding="0">
                <tr>
        &n

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend