CREATE PROCEDURE dbo.sp_InsertCounterStyle
(
@TOCHSCounterStyleID int,
@xmlList XML
)


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


Insert into TOCHSCounterStyle(TOCHSCounterStyleID,TOCHSCounterNumber,TOCHSCounterLabelText)

Select 
@TOCHSCounterStyleID,
xmlList.xl.value('@TOCHSCounterNumber','int') as TOCHSCounterNumber,
xmlList.xl.value('@TOCHSCounterLabelText','varchar(20)') as TOCHSCounterLabelText

from @xmlList.nodes('/xmlList') as xmlList(xl) 




END
GO

quote:

---

Originally posted by bluestar

then it saying

Msg 227, Level 15, State 1, Procedure sp_InsertCounterStyle, Line 20
"xl" is not a valid function, property, or field.



BTW what is xl in this case????why do we need it???


Thanks For reply

---

CREATE PROCEDURE dbo.sp_InsertCounterStyle
(
@TOCHSCounterStyleID int,
@xmlList XML
)


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


Insert into TOCHSCounterStyle(TOCHSCounterStyleID,TOCHSCounterNumber,TOCHSCounterLabelText)

Select 
@TOCHSCounterStyleID,
xmlList.xl.value('./@TOCHSCounterNumber','int') as TOCHSCounterNumber,
xmlList.xl.value('./@TOCHSCounterLabelText','varchar(20)') as TOCHSCounterLabelText

from @xmlList.nodes('/xmlList')xmlList(xl)

END
GO

quote:

---

Originally posted by bluestar

do I have to declare 'xl' somewhere else also ???

---

CREATE PROCEDURE dbo.sp_InsertCounterStyle
(
@TOCHSCounterStyleID int,
@xmlList XML
)


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select 
xmlList.xl.value('./@TOCHSCounterNumber','int') as TOCHSCounterNumber,
xmlList.xl.value('./@TOCHSCounterLabelText','varchar(20)') as TOCHSCounterLabelText
from @xmlList.nodes('/xmlList')xmlList(xl)
END
GO

quote:

---

Originally posted by bluestar

I tried executing only this

CREATE PROCEDURE dbo.sp_InsertCounterStyle
(
@TOCHSCounterStyleID int,
@xmlList XML
)


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select
path.xl.value('./@TOCHSCounterNumber','int') as TOCHSCounterNumber,
path.xl.value('./@TOCHSCounterLabelText','varchar(20)') as TOCHSCounterLabelText
from @xmlList.nodes('/xmlList')path(xl)
END
GO

I am getting the same error...
I hope I dont have to create any empty xml file for this.
Because I am being told that from datagrid ,data will be stored in memory,then I have to write a function which will traverse thru grid and insert data in the table. In the function I have to pass xml file as string.


Thanks for the help!!!

---

quote:

---

Originally posted by bluestar

thanks
the error is corrected.

One last question,How can I execute this Stored procedure to check whether its working correctly or not.

I am executing it then it is saying me to enter just 2 paramenters,
the TOCHSCounterStyleID and xmlList,

I want it to run by entering @TOCHSCounterNumber and TOCHSCounterLabelText, this is where the data will be added in GridView.

Thanks Again

---

quote:

---

Originally posted by bluestar

but if I add them in stored procedure as declare @TOCHSCounterLabelText and @TOCHSCounterNumber, then there will be no use of xml.I am using xml because thoese two columns have multiple data to be inserted.

Please do reply to this,I want to clear my dout

Thank You

---

quote:

---

Originally posted by bluestar

so in that case,I have to create an xml file with those two values and pass to stored procedure ...right????

---