Insert From XML File

Viewed 58

I'm attempting to update a customer table using an XML file, but it's not pulling the values from the XML into the variables to pass into the table. The error I get says "Cannot insert the value NULL into column 'EmailAddress'" even though there is clearly a value for EmailAddress. I feel like I'm missing something very simple, but can't figure it out. Any thoughts?

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
2 Answers
Related