CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxpath
Krystianya
Krystianya

Reputation: 171

Insert From XML File

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="[email protected]" Password="" FirstName="Isabella" LastName="Chan" />
        <Customer EmailAddress="[email protected]" Password="" FirstName="John" LastName="Prine" />
        <Customer EmailAddress="[email protected]" 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

Upvotes: 2

Views: 63

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67281

I feel like I'm missing something very simple ...

Yeah, you're right :-D, You are missing the @ to read the attribute-value:

DECLARE @CustomerUpdate XML

SET @CustomerUpdate = 
    '<NewCustomers>
        <Customer EmailAddress="[email protected]" Password="" FirstName="Isabella" LastName="Chan" />
        <Customer EmailAddress="[email protected]" Password="" FirstName="John" LastName="Prine" />
        <Customer EmailAddress="[email protected]" 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)')

UPDATE: Insert all <Customer> in one go

Try this:

DECLARE @Customers TABLE(EMailAddress VARCHAR(100),[Password] VARCHAR(100),FirstName VARCHAR(100),LastName VARCHAR(100));

DECLARE @CustomerUpdate XML

SET @CustomerUpdate = 
    '<NewCustomers>
        <Customer EmailAddress="[email protected]" Password="" FirstName="Isabella" LastName="Chan" />
        <Customer EmailAddress="[email protected]" Password="" FirstName="John" LastName="Prine" />
        <Customer EmailAddress="[email protected]" 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

Upvotes: 3

Krystianya
Krystianya

Reputation: 171

This code works perfectly now. 

    USE MyGuitarShop

DECLARE @CustomerUpdate XML

SET @CustomerUpdate = 
    '<NewCustomers>
        <Customer EmailAddress="[email protected]" Password="" FirstName="Isabella" LastName="Chan" />
        <Customer EmailAddress="[email protected]" Password="" FirstName="John" LastName="Prine" />
        <Customer EmailAddress="[email protected]" 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)')       
    );

INSERT Customers (EmailAddress, Password, FirstName, LastName)
    VALUES
    (
        @CustomerUpdate.value('(/NewCustomers/Customer/@EmailAddress)[2]', 'varchar(255)'),
        @CustomerUpdate.value('(/NewCustomers/Customer/@Password)[2]', 'varchar(60)'),
        @CustomerUpdate.value('(/NewCustomers/Customer/@FirstName)[2]', 'varchar(60)'),
        @CustomerUpdate.value('(/NewCustomers/Customer/@LastName)[2]', 'varchar(60)')       
    );

INSERT Customers (EmailAddress, Password, FirstName, LastName)
    VALUES
    (
        @CustomerUpdate.value('(/NewCustomers/Customer/@EmailAddress)[3]', 'varchar(255)'),
        @CustomerUpdate.value('(/NewCustomers/Customer/@Password)[3]', 'varchar(60)'),
        @CustomerUpdate.value('(/NewCustomers/Customer/@FirstName)[3]', 'varchar(60)'),
        @CustomerUpdate.value('(/NewCustomers/Customer/@LastName)[3]', 'varchar(60)')       
    );

SELECT * FROM Customers

Upvotes: 0

Related Questions