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]

[Wendelius]

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[^]

[Luc Pattyn]

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.

[Thabo Codeproject]

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

[Luc Pattyn]

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.

[Thabo Codeproject]

Thanks...I didnt find that when i read my code :-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: Thabo

[Wendelius]

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[^]

[Thabo Codeproject]

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

[Wendelius]

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.

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

[Thabo Codeproject]

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 :-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 ;)

Thabo

[Wendelius]

You're welcome. Glad that it helped :)

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