insert data into table from xml

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

insert data into table from xml

Author

Topic

muvvasiva
Starting Member

14 Posts

Posted - 2009-06-30 : 03:32:37

Stored procedure to insert data into a table from xml

create PROCEDURE [dbo].[Table_Add_xml] --'C:\Documents and Settings\sm38\Desktop\qaworksXML'
@tab_Xml xml
AS
BEGIN

DECLARE @idoc int

EXECUTE dbo.sp_xml_preparedocument @idoc OUTPUT, @tab_Xml

INSERT INTO PHYSICIANPROFILE(PRACTID,
DEPARTMENTID ,
STAFFCATEGORYID,
--FROMYEAR,
-- TOYEAR,
LICENSE,
NPI,
PRIVILEGECHANGEREQUESTED,
REPORTID
)
select PRACTITIONERID,DEPARTMENTID,STAFFCAREGORYID,LICENSE, NPI,PRIVILEGECHANGE, REPORTID
from OpenXml(@idoc,'/qaworks/physicanprofile',1)
With (PRACTITIONERID int,
DEPARTMENTID int,
STAFFCAREGORYID int,
--FROM int,
--TO int,
LICENSE varchar(20),
NPI varchar(20),
PRIVILEGECHANGE char(1),
REPORTID varchar(50) )
end

the above SP is executing but while running it is giving following errors.

The XML parse error 0xc00ce556 occurred on line number 1, near the XML text "C:\Documents and Settings\sm38\Desktop\qaworksXML".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Invalid at the top level of the document.'.
Msg 8179, Level 16, State 5, Procedure Table_Add_xml, Line 12
Could not find prepared statement with handle 0.
The statement has been terminated.

Pls help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:37:21

seems like your xml document had some errors or was not wellformed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources