|
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, ...
|
|
|
|
|
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 ). 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.
|
|
|
|
|
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
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
Thabo
|
|
|
|
|
You're welcome. Glad that it helped
|
|
|
|
|
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...
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
Thabo
|
|
|
|
|
much better!
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
|
|
|
|
|
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!
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
|
|
|
|
|
I just installed it and tried. It is a great tool. Thanks again.
TOMZ_KV
|
|
|
|
|
It's a pleasure
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.
|
|
|
|
|
The RedGate tools could probably help you, and if it's an immediate problem, you can use their evaluation period.
|
|
|
|
|
Thanks for the info.
TOMZ_KV
|
|
|
|
|
I use the script below to create auto gen scripts for crud. Mix and match to suit your needs. Once you have what you want save it as an sp with
the table name as a parameter and you are good to go.
SELECT * FROM sysColumns WHERE id = (select id from sysObjects where name = 'myTable');
|
|
|
|
|
Thanks for your help!
TOMZ_KV
|
|
|
|
|
|
A great trick. Wish sql server have this functionality natively. Thanks,
TOMZ_KV
|
|
|
|
|
I'm glad you got answers you like.
But... Personally I don't see the point in writing stored procedures that simply do those tasks -- especially update; update what? -- database tasks depend on the needs of business requirements which often don't include simple inserts, updates, and deletes on individual tables. Most database tables don't exist all on their own and several operations need to be executed at one time. It's well known that I don't like stored procedures at all, but at least they should be written to perform a higher-level operation than one simple operation.
|
|
|
|
|
I've seen lots of tools make attempts at using sql, but in response to your question all you have to do is wrap the generated sql in a create procedure call, add parameters and voila, you have your skeleton procedure. Of course I believe that all procedures should do whatever they can to protect the data so I insist that any procedures written on my watch contain validation of whatever can be validated.
I wasn't, now I am, then I won't be anymore.
|
|
|
|
|