我正在尝试使用XML文件更新客户表,但是它没有将XML中的值提取到变量中以传递到表中。我收到的错误消息是“无法将值NULL插入列'EmailAddress'”,即使EmailAddress显然有一个值也是如此。我觉得我缺少一些非常简单的东西,但是无法弄清楚。有什么想法吗?
USE MyGuitarShop
DECLARE @CustomerUpdate XML
SET @CustomerUpdate =
'<NewCustomers>
<Customer EmailAddress="izzychan@yahoo.com" Password="" FirstName="Isabella" LastName="Chan" />
<Customer EmailAddress="johnprine@gmail.com" Password="" FirstName="John" LastName="Prine" />
<Customer EmailAddress="kathykitchen@sbcglobal.net" Password="" FirstName="Kathy" LastName="Kitchen" />
</NewCustomers>';
INSERT Customers (EmailAddress, Password, FirstName, LastName)
VALUES
(
@CustomerUpdate.value('(/NewCustomers/Customer/EmailAddress)[1]', 'varchar(255)'),
@CustomerUpdate.value('(/NewCustomers/Customer/Password)[1]', 'varchar(60)'),
@CustomerUpdate.value('(/NewCustomers/Customer/FirstName)[1]', 'varchar(60)'),
@CustomerUpdate.value('(/NewCustomers/Customer/LastName)[1]', 'varchar(60)')
);
SELECT * FROM Customers
最佳答案
我觉得我缺少一些非常简单的东西...
是的,您是对的:-D,您缺少@
来读取属性值:
DECLARE @CustomerUpdate XML
SET @CustomerUpdate =
'<NewCustomers>
<Customer EmailAddress="izzychan@yahoo.com" Password="" FirstName="Isabella" LastName="Chan" />
<Customer EmailAddress="johnprine@gmail.com" Password="" FirstName="John" LastName="Prine" />
<Customer EmailAddress="kathykitchen@sbcglobal.net" Password="" FirstName="Kathy" LastName="Kitchen" />
</NewCustomers>';
SELECT
@CustomerUpdate.value('(/NewCustomers/Customer/@EmailAddress)[1]', 'varchar(255)'),
@CustomerUpdate.value('(/NewCustomers/Customer/@Password)[1]', 'varchar(60)'),
@CustomerUpdate.value('(/NewCustomers/Customer/@FirstName)[1]', 'varchar(60)'),
@CustomerUpdate.value('(/NewCustomers/Customer/@LastName)[1]', 'varchar(60)')
更新:一次性插入所有
<Customer>
试试这个:
DECLARE @Customers TABLE(EMailAddress VARCHAR(100),[Password] VARCHAR(100),FirstName VARCHAR(100),LastName VARCHAR(100));
DECLARE @CustomerUpdate XML
SET @CustomerUpdate =
'<NewCustomers>
<Customer EmailAddress="izzychan@yahoo.com" Password="" FirstName="Isabella" LastName="Chan" />
<Customer EmailAddress="johnprine@gmail.com" Password="" FirstName="John" LastName="Prine" />
<Customer EmailAddress="kathykitchen@sbcglobal.net" Password="" FirstName="Kathy" LastName="Kitchen" />
</NewCustomers>';
INSERT INTO @Customers (EmailAddress, Password, FirstName, LastName)
SELECT c.value('@EmailAddress', 'varchar(255)')
,c.value('@Password', 'varchar(60)')
,c.value('@FirstName', 'varchar(60)')
,c.value('@LastName', 'varchar(60)')
FROM @CustomerUpdate.nodes(N'/NewCustomers/Customer') AS A(c)
SELECT * FROM @Customers