Could anyone tell me more about working with OpenXML.
I have the following XML structure, which will be passed as a parameter to the stored procedure. I have used OpenXML, but none of the records are being inserted. Could anyone suggest anything else.
The following is just an example of the xml i will be passing as a parameter. The stored procedure compiles fine and executes fine as well... but don't think it is doing anything.
CREATE Procedure <ProceName>
@xmlDAT ntext/xml(Would the type make any difference?)
Declare @xmlPointer int
SELECT @xmlDAT = N'<ROOT><Name ID="IDNumber" Name="SomeName"><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/></Name></ROOT>
EXEC sp_xml_preparedocument @xmlPointer, @xmlDat
BEGIN
INSERT Table(Id) SELECT * FROM OpenXML (@xmlPointer, N'/PMACData/Name') WITH (Id varchar(50) @ID)
INSERT Table(DateTime, [Val]) SELECT * FROM OpenXML (@xmlPointer,N'//Node1[@dateTime]') WITH (dateTime DateTime '../@dateTime', [Val] real '../@currentvalue')
END
I have the following XML structure, which will be passed as a parameter to the stored procedure. I have used OpenXML, but none of the records are being inserted. Could anyone suggest anything else.
The following is just an example of the xml i will be passing as a parameter. The stored procedure compiles fine and executes fine as well... but don't think it is doing anything.
CREATE Procedure <ProceName>
@xmlDAT ntext/xml(Would the type make any difference?)
Declare @xmlPointer int
SELECT @xmlDAT = N'<ROOT><Name ID="IDNumber" Name="SomeName"><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/></Name></ROOT>
EXEC sp_xml_preparedocument @xmlPointer, @xmlDat
BEGIN
INSERT Table(Id) SELECT * FROM OpenXML (@xmlPointer, N'/PMACData/Name') WITH (Id varchar(50) @ID)
INSERT Table(DateTime, [Val]) SELECT * FROM OpenXML (@xmlPointer,N'//Node1[@dateTime]') WITH (dateTime DateTime '../@dateTime', [Val] real '../@currentvalue')
END