用于将xmldata插入sqlserver的存储过程 [英] stored procedure for inserting xmldata to sqlserver

问题描述

此存储过程的hiii未执行，则出现错误u plz可以帮助我吗

错误:消息102，级别15，状态1，过程Employee_insert，第18行
'';''
附近的语法不正确

创建proc Employee_insert @filepath nvarchar(100)
如
开始
设置@filepath =''E:\ Xmlview \ Register.xml'';
插入到Userdata(UserName，Password，Email，Gender，Location，DateOfBirth)
SELECT XmlQuery.employee.query(``名称'').value(''.''，``VARCHAR(50)'')，
dbo.REMOVE_SPECIAL(XmlQuery.employee.query(``Password'').value(''.''，``VARCHAR(30)''))，
dbo.REMOVE_SPECIAL(XmlQuery.employee.query(``Email'').value(''.''，``VARCHAR(50)''))，
XmlQuery.employee.query(``Gender'').value(''.''，``VARCHAR(50)'')，
XmlQuery.employee.query(``Location'').value(''.''，``VARCHAR(50)'')，
XmlQuery.employee.query(``DateOfBirth'').value(''.''，``nvarchar(50)'')
来自(
SELECT CAST(XmlQuery AS XML)
来自OPENROWSET(
BULK``E:\ Xmlview \ Register.xml''，
SINGLE_BLOB)AS T(XmlQuery)
)AS T(XmlQuery)
交叉应用XmlQuery.nodes(''employees/employee'')AS XmlQuery(employee)

hiii for this stored procedure is not executing it comes error can u plz help me

error: Msg 102, Level 15, State 1, Procedure Employee_insert, Line 18
Incorrect syntax near '';''


create proc Employee_insert @filepath nvarchar(100)
as
begin
set @filepath=''E:\Xmlview\Register.xml'';
insert into Userdata(UserName,Password,Email,Gender,Location,DateOfBirth)
SELECT XmlQuery.employee.query(''Name'').value(''.'', ''VARCHAR(50)''),
dbo.REMOVE_SPECIAL(XmlQuery.employee.query(''Password'').value(''.'', ''VARCHAR(30)'')),
dbo.REMOVE_SPECIAL(XmlQuery.employee.query(''Email'').value(''.'', ''VARCHAR(50)'')),
XmlQuery.employee.query(''Gender'').value(''.'', ''VARCHAR(50)''),
XmlQuery.employee.query(''Location'').value(''.'', ''VARCHAR(50)''),
XmlQuery.employee.query(''DateOfBirth'').value(''.'', ''nvarchar(50)'')
from (
SELECT CAST(XmlQuery AS XML)
FROM OPENROWSET(
BULK ''E:\Xmlview\Register.xml'',
SINGLE_BLOB) AS T(XmlQuery)
) AS T(XmlQuery)
CROSS APPLY XmlQuery.nodes(''employees/employee'') AS XmlQuery(employee)

推荐答案

试试这个

Try this

create proc Employee_insert @filepath nvarchar(100)
as
begin
set @filepath='E:\Xmlview\Register.xml';
insert into   Userdata(UserName,Password,Email,Gender,Location,DateOfBirth)
SELECT XmlQuery.employee.query('Name').value('.', 'VARCHAR(50)'),
         dbo.REMOVE_SPECIAL(XmlQuery.employee.query('Password').value('.', 'VARCHAR(30)')),
         dbo.REMOVE_SPECIAL(XmlQuery.employee.query('Email').value('.', 'VARCHAR(50)')),
         XmlQuery.employee.query('Gender').value('.', 'VARCHAR(50)'),
         XmlQuery.employee.query('Location').value('.', 'VARCHAR(50)'),
         XmlQuery.employee.query('DateOfBirth').value('.', 'nvarchar(50)')
from (
SELECT CAST(XmlQuery AS XML)
FROM OPENROWSET(
BULK 'E:\Xmlview\Register.xml',
SINGLE_BLOB) AS T(XmlQuery)
) AS T(XmlQuery)
CROSS APPLY XmlQuery.nodes('employees/employee') AS XmlQuery(employee)
end

结尾关键字丢失

这篇关于用于将xmldata插入sqlserver的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！