Hi everyone i am a new comer for this forum ,I have a small problem with format the xml to a table in SQL SERVER stored procedure [modified] - Database Discussion Boards

Thanks.

SOFTDEV

Luck in life always exists in the form of an abstract class that cannot be instantiated directly and needs to be inherited by hard work and dedication.

I’m trying to insert a new record into an MS Access (2002-2003) database using Perl. My instructor's example has me getting the last record ID and using that to generate the next ID.

Two things:
1- When I do this the way the instructor illustrates (based on existing ID) using this code:

if($db->Sql("SELECT MAX(ID) lastID FROM Test"))<br />
{<br />
print "SQL Error: " . $db->Error() . "\n";<br />
$db->Close();<br />
exit;<br />
}

Can anybody tell me why I'm getting this error:

SQL Error: [-3100] [1] [0] "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'MAX(ID) lastID'."

2- If my ID field is set to “AutoNumber” shouldn’t I be able to simply insert and new record (with no ID)? I’ve attempted that with no success.

hi,

1. I think you need an as:

SELECT MAX(ID) AS lastID FROM Test

2. I should think so. And having two separate DB operations to first fetch then use MAX(ID) seems like a recipe for failure as soon as other users are also operating the DB.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

Thanks Luc. Aliasing did the trick.

Luc Pattyn wrote:
having two separate DB operations to first fetch then use MAX(ID) seems like a recipe for failure

Indeed it does. Using for example @@IDENTITY would be safer.

The need to optimize rises from a bad design.My articles[^]

Hi
For the first question: You could try adding the keyword AS for the column:

if($db->Sql("SELECT MAX(ID) AS lastID FROM Test"))

And for the second question. Yes if the field is autonumbered, you don't have to mention it in an INSERT statement just like you don't have to mention any field that has a default value. However, if you're later going to add something for this row to a child table of this table you need to know the value for the foreign key in the child table.

The need to optimize rises from a bad design.My articles[^]

Thanks Mika. As both you and Luc suggested, using AS worked by referencing the existing ID's. I'd much rather not do it that way but when I try to insert a record with no ID my table does not update. I'll try to figure that out latter since I can get by with Aliasing for now. Wink | ;)

This is the senario, i am trying to copy a table and store that table into a XML variable(Not physically xml file) and read that XML variable ,at the same time also trying to retrive and insert all data to another table.The stored procedure is given below

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:     Thabo
-- Create date: 18.02.2011
-- Description:	TEST XML
-- =============================================
CREATE PROCEDURE sppassingStoredProcedue
	 DECLARE @x XML 
AS
BEGIN

SET NOCOUNT ON;
SET @x=(SELECT PramryKeyForThisTable,AutoNumberColumn,Data1,Data2,Data3 FROM tblInsertTableForAutoNumber FOR XML AUTO ,TYPE ,ELEMENTS XSINIL,ROOT ('TABLEROOT'))

INSERT INTO CopyOftblInserTableForAutonumber
SELECT
	tab.col.value('@PrimaryKeyForThisTable[1]','SMALLINT') AS PramryKeyForThisTable,
	tab.col.value('@AutoNumberColumn[1]','SMALLINT')AS AutoNumberColumn,
	tab.col.value('@Data1[1]','VARCHAR(50)') AS Data1,
	tab.col.value('@Data2[1]','VARCHAR(50)') AS Data2,
	tab.col.value('@Data3[1]','VARCHAR(50)') AS Data3
FROM @x.nodes('//TABLEROOT/tblInsertTableForAutoNumber')AS tab(Col)	
	
END

Actually the second table is the copy of the first table.It has same stucure of the first table
The table structure is,

colum name datatype
----------- --------
PramryKeyForThisTable smallint
AutoNumberColumn smallint
Data1 varchar(50)
Data2 varchar(50)
Data3 varchar(50)

I have got different different error messages every time.i hve got this error message for above cording.

Msg 156, Level 15, State 1, Procedure sppassingStoredProcedue, Line 7
Incorrect syntax near the keyword 'DECLARE'.
Msg 156, Level 15, State 1, Procedure sppassingStoredProcedue, Line 8
Incorrect syntax near the keyword 'AS'."

I couldnt figure out how to do this. Can any one pls give a good solution for this.

Thabo

-- Modified Friday, February 18, 2011 11:33 AM

The first thing you should do is to change the place of the declaration of @X.

If it's a parameter then:

CREATE PROCEDURE sppassingStoredProcedue @x XML
AS
BEGIN
...

and if it's just a variable inside the procedure:

CREATE PROCEDURE sppassingStoredProcedue
AS
     DECLARE @x XML 
BEGIN
...

The need to optimize rises from a bad design.My articles[^]

Thanks...I didnt find that when i read my code Big Grin | :-D

Now it was compiling succesfully, but when i execute sp all the colums values of copy table are given null. The first table have 19 rows but with data.The copy table also have 19 rows but without data i mean all are NULL!
Is their any thing error in logic ? The thing is ,i dind work with XML in SQL SEERVER before. Sigh | :sigh:

Thabo

No problem,

You could try to run the operations in smaller parts in order to find out the problem. For example you could run the following without creating a stored procedure:

DECLARE @x XML 
begin
   SET @x=(SELECT PramryKeyForThisTable,AutoNumberColumn,Data1,Data2,Data3 
           FROM tblInsertTableForAutoNumber 
           FOR XML AUTO ,TYPE ,ELEMENTS XSINIL,ROOT ('TABLEROOT'))

   print convert(varchar(max), @x)

   SELECT tab.col.value('@PrimaryKeyForThisTable[1]','SMALLINT') AS PramryKeyForThisTable,
          tab.col.value('@AutoNumberColumn[1]','SMALLINT')AS AutoNumberColumn,
          tab.col.value('@Data1[1]','VARCHAR(50)') AS Data1,
          tab.col.value('@Data2[1]','VARCHAR(50)') AS Data2,
          tab.col.value('@Data3[1]','VARCHAR(50)') AS Data3
   FROM @x.nodes('//TABLEROOT/tblInsertTableForAutoNumber')AS tab(Col)
end

Hopefully that first prints the contents of @x and then show you all the data that's selected from the nodes query.

You could try to modify the columns in the select statement. If you test something like:

SELECT tab.col.value('(PrimaryKeyForThisTable/text())[1]','SMALLINT') AS PramryKeyForThisTable, ...

The need to optimize rises from a bad design.My articles[^]

Hi thanks,,, i tried based on your advice.I just chnaged my code little bit.
like this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Thabo
-- Create date: 18.02.2011
-- Description:	TEST XML
-- =============================================
ALTER PROCEDURE [dbo].[sppassingStoredProcedue]
AS
DECLARE @x XML 
BEGIN

	SET NOCOUNT ON;
	SET @x=(SELECT PramryKeyForThisTable,AutoNumberColumn,Data1,Data2,Data3 FROM tblInsertTableForAutoNumber FOR XML AUTO,ROOT('MYROOT'))

INSERT INTO CopyOftblInserTableForAutonumber
SELECT
	R.Tab.value('@PrimaryKeyForThisTable[1]','SMALLINT)') AS PramryKeyForThisTable,
	R.Tab.value('@AutoNumberColumntext[1]','SMALLINT')AS AutoNumberColumn,
	R.Tab.value('@Data1 [1]','VARCHAR(50)') AS Data1,
	R.Tab.value('@Data2 [1]','VARCHAR(50)') AS Data2,
	R.Tab.value('@Data3 [1]','VARCHAR(50)') AS Data3
FROM @x.nodes('//MYROOT/tblInsertTableForAutoNumber')AS R(Tab)
	
	
END

i also print the @x variable.I got the correct XML with all data.But when i go to the XQuery also i got all the row and values except
PrimaryKeyForThisTable, AutoNumberColumntext column values.i got null values for those columns.I thing there might be a dataconversion problem.I tried the text() function .But nothing happened.How i get those values?

Thabo

Hi,

Since I don't have the data you have and don't know that table structure it's hard to where the problem lies. I created a small test-case which works fine (at least on my machine Smile | :)

). Could you try this and perhaps it helps you to pinpoint the problem. If the problem still remains, could you post the creation script for the table along with few test rows.

create table Test (
  id  int         not null primary key identity(1,1),
  val varchar(10) not null
);

insert into Test (val) values ('First');
insert into Test (val) values ('Second');
delete from Test;
insert into Test (val) values ('First');
insert into Test (val) values ('Second');

DECLARE @x XML 
begin   
   SET @x=(SELECT id, val 
           FROM Test 
           FOR XML AUTO ,TYPE ,ELEMENTS XSINIL,ROOT('TABLEROOT'));
        
   print convert(varchar(max), @x)

   SELECT tab.col.value('id[1]','SMALLINT') AS NewId,
          tab.col.value('val[1]','VARCHAR(10)') AS NewVal
   FROM @x.nodes('//TABLEROOT/Test')AS tab(Col);
end

mika

p.s. Don't mind about the delete in the middle of the inserts. I just wanted to see that I get the actual identity values so if this is run once the id's should be 3 and 4.

The need to optimize rises from a bad design.My articles[^]

Hi thank u very much....
I made a simple mistake...
i re wrote the coding as this

set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Thabo
-- Create date: 18.02.2011
-- Description:	TEST XML
-- =============================================
CREATE PROCEDURE [dbo].[sppassingStoredProcedue]
AS
DECLARE @x XML 
BEGIN

	SET NOCOUNT ON;
	SET @x=(SELECT PramryKeyForThisTable,AutoNumberColumn,Data1,Data2,Data3 FROM tblInsertTableForAutoNumber FOR XML AUTO,ROOT('MYROOT'))

INSERT INTO CopyOftblInserTableForAutonumber 
SELECT
	R.Tab.value('@PramryKeyForThisTable [1]','SMALLINT') AS PramryKeyForThisTable,
	R.Tab.value('@AutoNumberColumn [1]','SMALLINT')AS AutoNumberColumn,
	R.Tab.value('@Data1 [1]','VARCHAR(50)') AS Data1,
	R.Tab.value('@Data2 [1]','VARCHAR(50)') AS Data2,
	R.Tab.value('@Data3 [1]','VARCHAR(50)') AS Data3
FROM @x.nodes('//MYROOT/tblInsertTableForAutoNumber')AS R(Tab)
	
	
END

The mistake which i made before is ,the colum name which is in XQUERY was not same as in select statement.Now All are Working fine Big Grin | :-D

Thank you agin for your help.I am a new person to Codeproject.But i got great response !!!!Thank you .Now i am always using code project Wink | ;)

Thabo

You're welcome. Glad that it helped Smile | :)

The need to optimize rises from a bad design.My articles[^]

and the very first thing to do when posting code is using PRE tags, as Mika did. You can still edit your message if you want...

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

Thank u Luc Pattyn i edit thos within Pre tag Smile | :)

Thabo

much better!

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

Is is possible to write a query that would retrieve say the 20 records that are centered on a specifed record. In other words if I want orderId=9425 then I want the 5 orders for a particular customer that are less then 9425 and the 5 orders after 9425?

I can't just say where orderID between 9420 and 9430 as these orders may not belong to this customer. This customers last order may have been orderID 8550 so would not show up in this query.

Hi,

One way of doing this could be:

select * 
from  orders a
where a.customer = 'A'
and ( a.id in (select top(2) id
	       from  orders b
               where b.customer = a.customer
               and   b.id       < 26
               order by b.id desc)
   or
      a.id in (select top(3) id
	       from  orders b
               where b.customer = a.customer
               and   b.id       >= 26
               order by b.id asc))

The above (not tested) should fetch 5 rows. ID 26 and 2 rows from both sides

The need to optimize rises from a bad design.My articles[^]

Have you seen a sql tool that can generate stored procedures against a table for select, insert, update and delete? Sql2008 provides scripts for the four actions. But the code is far from a stored procedure. Thanks,

TOMZ_KV

moved on Friday, February 18, 2011 4:26 PM

Try SSMS ToolsPack[^].

I like it a lot.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
I wouldn't let CG touch my Abacus!
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is.

Wow, thanks! Smile | :)

And I thought RedGate had some evil monopoly on SSMS add-ons. It looked possible to do when I checked it out years ago, but insanely undocumented. I know it's the VS shell, but there isn't an explicit, public SSMS extension interface.

Brady Kelly wrote:
but insanely undocumented.

Oh yusssss!

The guy that wrote it does a lot of good stuff.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
I wouldn't let CG touch my Abacus!
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is.

I'll take a look. thanks,

TOMZ_KV