Convert a XMl Into Table
Dear All,--create table tmp_DV(sno int,Name varchar(20))
/*
exec dp_convertxml @strXML=N'
<NewDataSet>
<EmpDetails>
<sno>11</sno>
<Name>Haja</Name>
</EmpDetails>
</NewDataSet>'
*/
Alter Procedure dp_convertxml
(
@strXML xml
)
as
Begin
Declare @intPointer int
exec sp_xml_preparedocument @intPointer output, @strXML
--Insert into tmp_DV(sno,Name)
Select sno,Name
from OpenXml(@intPointer,'NewDataset/EmpDetails',2)
With (sno int 'sno',Name varchar(20) 'Name')
exec sp_xml_removedocument @intPointer
select * from tmp_DV
End
I attached my Sp with Table creation. While converting the xml into table. it doesnt show any error, but no can be inserting. Kindly guide me.