need only data

hi
here is my sp.
CREATE PROCEDURE dbo.Insert_Employee
(
@.strXML ntext
)
AS
DECLARE @.Pointer int
EXEC sp_xml_preparedocument @.Pointer OUTPUT,
'<Data><woitem id="3420" type="Resistance"><Resistance><R1>0</R1><R2>1</R2><R3>2</R3><R4>3</R4><R5>4</R5><Avg>21</Avg><StdDev>22</StdDev><Cov>23</Cov></Resistance></woitem><woitem id="3421" type="Process"><Process><NipPress>1</NipPress><ZONE1>2</ZON
E1><ZONE2>3</ZONE2><ZONE3>4</ZONE3><ADAPTER></ADAPTER><MeltTemp></MeltTemp><ExSpeed></ExSpeed><Load1></Load1><BackPress></BackPress><TopLip></TopLip><BotLip></BotLip></Process></woitem></Data>'
Begin
--delete from employee where id in(select id from openxml(@.Pointer,'/Data/woitem',2) With (id int '@.id'))
Insert into employee
Select * from OpenXml(@.Pointer,'/Data/woitem',8)
With (id int '@.id', type varchar(50) '@.type', Data varchar(1000) '@.mp:xmltext')
exec sp_xml_removedocument @.Pointer
RETURN
End
GO
when i run this in Data column it is storing "<woitem>...............</woitem>". i want to eliminate <woitem>. how do i do this?
bye
ravi
Sorry for the delay in answering.
@.mp:xmltext is keeping the wrapper because it needs it for the overflow
handling semantics (to for example continue to keep not mapped attributes).
In SQL Server 2000, you have the option to use some string manipulations to
drop the wrapper element (watch for empty elements!).
In SQL Server 2005, you can use the XML datatype in the following way:
Select * from OpenXml(@.Pointer,'/Data/woitem',8)
With (id int '@.id', type varchar(50) '@.type', Data xml'*')
HTH
Michael
2.
"Ravindra" <anonymous@.discussions.microsoft.com> wrote in message
news:3E5D0CCB-18F1-439A-A69E-1037A62731FA@.microsoft.com...
> hi
> here is my sp.
> CREATE PROCEDURE dbo.Insert_Employee
> (
> @.strXML ntext
> )
> AS
> DECLARE @.Pointer int
> EXEC sp_xml_preparedocument @.Pointer OUTPUT,
> '<Data><woitem id="3420"
> type="Resistance"><Resistance><R1>0</R1><R2>1</R2><R3>2</R3><R4>3</R4><R5>4</R5><Avg>21</Avg><StdDev>22</StdDev><Cov>23</Cov></Resistance></woitem><woitem
> id="3421"
> type="Process"><Process><NipPress>1</NipPress><ZONE1>2</ZONE1><ZONE2>3</ZONE2><ZONE3>4</ZONE3><ADAPTER></ADAPTER><MeltTemp></MeltTemp><ExSpeed></ExSpeed><Load1></Load1><BackPress></BackPress><TopLip></TopLip><BotLip></BotLip></Process></woitem></Data>'

> Begin
> --delete from employee where id in(select id from
> openxml(@.Pointer,'/Data/woitem',2) With (id int '@.id'))
> Insert into employee
> Select * from OpenXml(@.Pointer,'/Data/woitem',8)
> With (id int '@.id', type varchar(50) '@.type', Data varchar(1000)
> '@.mp:xmltext')
> exec sp_xml_removedocument @.Pointer
> RETURN
> End
> GO
> when i run this in Data column it is storing
> "<woitem>...............</woitem>". i want to eliminate <woitem>. how
> do i do this?
> bye
> ravi
>